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 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？

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

Only If B has a foreign key reference to A

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

Hi,

I too agree with Adrian Klaver, the trigger is the only option

Thanks,

V Muralidharan
+91 9940302900

On Tue, 1 Nov 2022, 19:46 Peter J. Holzer, <hjp-pgsql@hjp.at> wrote:

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