Thread: Dropping dependent tables
Hi,
I would like to write a SQL statement to drop all the tables owned by me but a problem I’m struggling with is with referential integrity. The statement I have now to generate the drop statements is
select 'drop table '||tablename||' cascade;' from pg_tables where tableowner='<myuseraccount>';
The generated SQLs above might attempt to drop the parent tables first before the child and to be able to drop all the tables, I had to run the SQL script in multiple iterations. Not very clean.
Can someone advise how I could formulate the SQL to check for table dependencies to generate a SQL script that drops the child tables first before the parent? Or are there any better alternatives?
Thank you.
Tiff
Tiffany Thang <tiffanythang@gmail.com> writes: > I would like to write a SQL statement to drop all the tables owned by me > but a problem I’m struggling with is with referential integrity. The > statement I have now to generate the drop statements is > select 'drop table '||tablename||' cascade;' from pg_tables where > tableowner='<myuseraccount>'; > The generated SQLs above might attempt to drop the parent tables first > before the child and to be able to drop all the tables, I had to run the > SQL script in multiple iterations. Not very clean. Uh ... it's not clear to me why that wouldn't work. CASCADE should be able to take care of foreign keys: postgres=# create table t1 (f1 int primary key); CREATE TABLE postgres=# create table t2 (f2 int references t1); CREATE TABLE postgres=# drop table t1; ERROR: cannot drop table t1 because other objects depend on it DETAIL: constraint t2_f2_fkey on table t2 depends on table t1 HINT: Use DROP ... CASCADE to drop the dependent objects too. postgres=# drop table t1 cascade; NOTICE: drop cascades to constraint t2_f2_fkey on table t2 DROP TABLE Could you enlarge on what problem you saw, specifically? regards, tom lane
On Thu, Jun 3, 2021 at 10:18 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Tiffany Thang <tiffanythang@gmail.com> writes:
> I would like to write a SQL statement to drop all the tables owned by me
> but a problem I’m struggling with is with referential integrity. The
> statement I have now to generate the drop statements is
> select 'drop table '||tablename||' cascade;' from pg_tables where
> tableowner='<myuseraccount>';
> The generated SQLs above might attempt to drop the parent tables first
> before the child and to be able to drop all the tables, I had to run the
> SQL script in multiple iterations. Not very clean.
Uh ... it's not clear to me why that wouldn't work. CASCADE should
be able to take care of foreign keys:
postgres=# create table t1 (f1 int primary key);
CREATE TABLE
postgres=# create table t2 (f2 int references t1);
CREATE TABLE
postgres=# drop table t1;
ERROR: cannot drop table t1 because other objects depend on it
DETAIL: constraint t2_f2_fkey on table t2 depends on table t1
HINT: Use DROP ... CASCADE to drop the dependent objects too.
postgres=# drop table t1 cascade;
NOTICE: drop cascades to constraint t2_f2_fkey on table t2
DROP TABLE
Could you enlarge on what problem you saw, specifically?
regards, tom lane
Obviously I did something incorrectly previously.
Thanks.
Tiff