2

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.

quarague
  • 295
  • 2
  • 15

1 Answers1

2

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');
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 1
    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'. – quarague Aug 31 '21 at 06:04