On Thu, Jun 3, 2021 at 10:18 AM Tom Lane <t...@sss.pgh.pa.us> wrote: > Tiffany Thang <tiffanyth...@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 >
Thanks Tom for your response. I tried it again and I was not able to reproduce the issue. Obviously I did something incorrectly previously. Thanks. Tiff
