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
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail&utm_term=icon>
Virus-free.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail&utm_term=link>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
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 isselect '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 TABLECould 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