From: | Tiffany Thang <tiffanythang(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Dropping dependent tables |
Date: | 2021-06-04 01:10:04 |
Message-ID: | CAB_W-NPw87ZX16KrEPL6xk9A3exCJfVQz2XJHi=Fc7p3VSdfRw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Jun 3, 2021 at 10:18 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Tiffany Thang <tiffanythang(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | saket bansal | 2021-06-04 03:13:05 | Noinheritance with superuser |
Previous Message | Bruce Momjian | 2021-06-03 23:00:08 | Re: possible license violations |