1

I used pg_partman to create and drop partitions from my 2 postgres tables. Since there existed a foreign key relations among the tables, I couldn't drop the tables using partman(does not allow). However, I was able to detach the older partitioned tables(data older than 30 days) from the parent table. Now, in order to clean these tables, I need to get the names of the detached tables.

What sql query would help me achieve this ?

4
  • 1
    Since those tables are no longer attached to the partition parent, you need another way to identify them. Typically, you have naming pattern for partitions ... Commented Jun 25 at 0:44
  • Can we use pg_inherits and pg_class tables ? Commented Jun 25 at 11:30
  • Entries in those system catalogs are updated to look like regular tables. I don't know of anything still pointing to their partition past. Commented Jun 25 at 14:32
  • @ErwinBrandstetter The below query works but I want this to be reviewed and understand its correctness. Please have a look. The parent table's name is user_session_data : SELECT relname FROM pg_class c LEFT JOIN pg_inherits i ON c.oid = i.inhrelid WHERE i.inhrelid IS NULL AND relkind = 'r' AND relname LIKE 'user_session_data_%'; Commented Jun 25 at 17:45

1 Answer 1

0

Those tables are no longer attached to the partition parent, you need another way to identify them. Your comment indicates the naming pattern 'user_session_data_%'.
This returns all tables matching that pattern that are not partitions (any more):

SELECT relnamespace::regnamespace::text AS schema_name, relname AS table_name
FROM   pg_class c
WHERE  NOT relispartition  -- !
AND    relkind = 'r'
AND    relname LIKE 'user_session_data_%';  -- naming pattern for partitions

No need to join to pg_inherits. While Postgres maintains additional meta data for partitions (and legacy inheritance) there, looking at the flag pg_class.relispartition is simpler and more to the point. It's defined NOT NULL, so NOT relispartition can't fail for null values.

I also output the schema name to avoid possible confusion with other schemas. Alternatively, filter for the schema instead. Like:

...
AND   relnamespace = 'public'::regnamespace  -- your schema name here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.