Thread: Delete a table automatic?

On Tue, Nov 1, 2022 at 2:33 PM 黄宁 <huangning0722@gmail.com> wrote:

I now have two tables named A and B. Table B is calculated based on the data of table A. I wonder if table B can be automatically deleted when table A is deleted？

Your question seems not that specific.

You can use https://dbfiddle.uk/btGcOH30 to showcase your specific problem/question.

you can use DROP TABLE CASCADE.

DROP TABLE manual: https://www.postgresql.org/docs/current/sql-droptable.html

--

 I recommend David Deutsch's <<The Beginning of Infinity>>

  Jian

On 2022-11-01 07:41:14 -0600, Rob Sargent wrote:
> On 11/1/22 03:31, jian he wrote:
>
>     On Tue, Nov 1, 2022 at 2:33 PM 黄宁 <huangning0722@gmail.com> wrote:
>
>         I now have two tables named A and B. Table B is calculated based on the
>         data of table A. I wonder if table B can be automatically deleted when
>         table A is deleted？
[...]
>     you can use DROP TABLE CASCADE.
>     DROP TABLE manual: https://www.postgresql.org/docs/current/
>     sql-droptable.html
>
>
>
> Only If B has a foreign key reference to A

And even then it only drops the constraint, not the table (or the data):

hjp=> create table a (id serial primary key, t text);
CREATE TABLE
hjp=> create table b (id serial primary key, a int references a, t text);
CREATE TABLE
hjp=> \d a
                             Table "public.a"
╔════════╤═════════╤═══════════╤══════════╤═══════════════════════════════╗
║ Column │  Type   │ Collation │ Nullable │            Default            ║
╟────────┼─────────┼───────────┼──────────┼───────────────────────────────╢
║ id     │ integer │           │ not null │ nextval('a_id_seq'::regclass) ║
║ t      │ text    │           │          │                               ║
╚════════╧═════════╧═══════════╧══════════╧═══════════════════════════════╝
Indexes:
    "a_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "b" CONSTRAINT "b_a_fkey" FOREIGN KEY (a) REFERENCES a(id)

hjp=> \d b
                             Table "public.b"
╔════════╤═════════╤═══════════╤══════════╤═══════════════════════════════╗
║ Column │  Type   │ Collation │ Nullable │            Default            ║
╟────────┼─────────┼───────────┼──────────┼───────────────────────────────╢
║ id     │ integer │           │ not null │ nextval('b_id_seq'::regclass) ║
║ a      │ integer │           │          │                               ║
║ t      │ text    │           │          │                               ║
╚════════╧═════════╧═══════════╧══════════╧═══════════════════════════════╝
Indexes:
    "b_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "b_a_fkey" FOREIGN KEY (a) REFERENCES a(id)

[some inserts later]

hjp=> select * from b;
╔════╤═══╤══════╗
║ id │ a │  t   ║
╟────┼───┼──────╢
║  1 │ 1 │ foo1 ║
║  2 │ 1 │ foo2 ║
║  3 │ 2 │ bar1 ║
╚════╧═══╧══════╝
(3 rows)

hjp=> drop table a cascade;
NOTICE:  drop cascades to constraint b_a_fkey on table b
DROP TABLE

hjp=> \d b
                             Table "public.b"
╔════════╤═════════╤═══════════╤══════════╤═══════════════════════════════╗
║ Column │  Type   │ Collation │ Nullable │            Default            ║
╟────────┼─────────┼───────────┼──────────┼───────────────────────────────╢
║ id     │ integer │           │ not null │ nextval('b_id_seq'::regclass) ║
║ a      │ integer │           │          │                               ║
║ t      │ text    │           │          │                               ║
╚════════╧═════════╧═══════════╧══════════╧═══════════════════════════════╝
Indexes:
    "b_pkey" PRIMARY KEY, btree (id)

As you can see, the table is still there, but the foreign key constraint
is gone.

hjp=> select * from b;
╔════╤═══╤══════╗
║ id │ a │  t   ║
╟────┼───┼──────╢
║  1 │ 1 │ foo1 ║
║  2 │ 1 │ foo2 ║
║  3 │ 2 │ bar1 ║
╚════╧═══╧══════╝
(3 rows)

And the data in the table is also unchanged.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

On 10/31/22 18:44, 黄宁 wrote:
> I now have two tables named A and B. Table B is calculated based on the 
> data of table A. I wonder if table B can be automatically deleted when 
> table A is deleted？


The only thing I can think of is a sql_drop event trigger:

https://www.postgresql.org/docs/current/functions-event-triggers.html#PG-EVENT-TRIGGER-SQL-DROP-FUNCTIONS

9.29.2. Processing Objects Dropped by a DDL Command

Though this will not be specific to one table.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 10/31/22 18:44, 黄宁 wrote:
>> I now have two tables named A and B. Table B is calculated based on the 
>> data of table A. I wonder if table B can be automatically deleted when 
>> table A is deleted？

> The only thing I can think of is a sql_drop event trigger:

I wonder whether the OP's purposes wouldn't be better served by
making B a view or materialized view, instead of a separate table.
Then the cascaded-drop behavior would be automatic.

            regards, tom lane