How to list all tables in postgres without partitions

Question

This is closely related to this question which describes how to list all tables in a schema in a postgres databank. The query select * from information_schema.tables does the job. In my case, some of the tables in the schema are partitioned and in this case the query above lists both the complete table as well as all the partitions as separate entries.

How can I get a list that only contains the full tables without the individual partitions?

For example, if the schema contains a table named 'example' which is partitioned on the column 'bla' with the two values 'a' and 'b', then information_schema.tables will have one entry for 'example' and then two additional entries 'example_part_bla_a' and 'example_part_bla_a'. I thought about doing an exclusion based on substring matches to 'part' or something like that but that makes an assumption on how the tables are named and hence would fail with some table names. There must be a better way to do this.

Laurenz Albe · Accepted Answer · 2021-08-30 13:54:41Z

6

You won't find that information in the information_schema; you will have to query the catalogs directly:

SELECT c.relname
FROM pg_class AS c
WHERE NOT EXISTS (SELECT 1 FROM pg_inherits AS i
                  WHERE i.inhrelid = c.oid)
  AND c.relkind IN ('r', 'p');

answered Aug 30, 2021 at 13:54

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

quarague Over a year ago

That works. I simplified your statement a little bit by noticing that pg_class has a column called relispartition so one can replace the not exists statement by 'relispartition = FALSE'.

Collectives™ on Stack Overflow

How to list all tables in postgres without partitions

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related