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

Question

I am trying to use list partitioning in PostgreSQL.

• https://www.postgresql.org/docs/current/ddl-partitioning.html

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');

3. 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

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

Thank you..!

Answer 1

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';