Search Postgresql Archives

Re: Need exact SQL query to find List of Detach Partitioned Tables (Yet to be Dropped)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 11/15/24 11:46, Bharani SV-forum wrote:
  Team

Need exact SQL query to find List of Detach Partitioned Tables (Yet to be Dropped)

The following is the query which i used, i am using and i found an bug which is listing an newly created table (last week)

As David G. Johnston said how would you know it was formally a partition?:

https://www.postgresql.org/docs/current/sql-altertable.html

"
DETACH PARTITION partition_name [ CONCURRENTLY | FINALIZE ]

This form detaches the specified partition of the target table. The detached partition continues to exist as a standalone table, but no longer has any ties to the table from which it was detached.

[...]
"

The only I could see this working is if you had a standard naming scheme for partitions and then you could do a regex search in pg_class for that pattern where relkind = 'r'.


SELECT relnamespace::regnamespace::text AS schema_name, relname AS table_name
FROM   pg_class c
WHERE  NOT relispartition  -- !
AND    relkind = 'r' and lower(relnamespace::regnamespace::text) not in ('pg_catalog','partman','information_schema')  and
lower(relnamespace::regnamespace::text) in ('XYZ')
order by  relnamespace::regnamespace::text, relname ;

--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux