postgresqllistpartitioningtable-partitioning

How to get value list of list partitioning table of postgresql?


I am trying to use list partitioning in PostgreSQL.

So, I have some questions about that.

  1. Is there a limit on the number of values or partition tables in list partitioning?
  2. When a partitioning table is created as shown below, can i check the value list with SQL? (like keys = [test, test_2])
CREATE TABLE part_table (id int, branch text, key_name text) PARTITION BY LIST (key_name);
CREATE TABLE part_default PARTITION OF part_table DEFAULT;
CREATE TABLE part_test PARTITION OF part_table FOR VALUES IN ('test');
CREATE TABLE part_test_2 PARTITION OF part_table FOR VALUES IN ('test_2');
  1. When using the partitioning table created above, if data is added with key_name = "test_3", it is added to the default table. If 'test_3' exists in the default table and partitioning is attempted with the corresponding value, the following error occurs. In this case, is there a good way to partition with the value 'test_3' without deleting the value in the default table?
CREATE TABLE part_test_3 PARTITION OF part_table FOR VALUES IN ('test_3');

Error: updated partition constraint for default partition "part_default" would be violated by some row

  1. Is it possible to change the table name or value of a partition table?

Thank you..!


Solution

  • Is there a limit on the number of values or partition tables in list partitioning?

    Some test: https://www.depesz.com/2021/01/17/are-there-limits-to-partition-counts/

    The value in current table and value reside in which partition.

    SELECT
        tableoid::pg_catalog.regclass,
        array_agg(DISTINCT key_name)
    FROM
        part_table
    GROUP BY
        1;
    

    To get all the current partition, and the configed value range. Use the following.

    SELECT 
        c.oid::pg_catalog.regclass, 
        c.relkind, 
        inhdetachpending as is_detached, 
        pg_catalog.pg_get_expr(c.relpartbound, c.oid)
    FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
    WHERE c.oid = i.inhrelid 
    AND i.inhparent = '58281'
    
    
    --the following query will return 58281.
    select c.oid 
        from pg_catalog.pg_class c 
        where relname ='part_table';