刪除帶有依賴項的表
更新時間 2025-02-05 09:37:14
最近更新時間: 2025-02-05 09:37:14
分享文章
本頁為您介紹如何刪除帶有依賴項的表。
-- view
CREATE TABLE t1(id int);
CREATE VIEW v1 AS SELECT * FROM t1;
DROP TABLE t1;
ERROR: cannot drop table t1 because other objects depend on it
DETAIL: view v1 depends on table t1
HINT: Use DROP ... CASCADE to drop the dependent objects too.
DROP TABLE t1 CASCADE;
NOTICE: drop cascades to view v1
\dt "trash".*
List of relations
Schema | Name | Type | Owner
--------+-------------+-------+----------
trash | _3 | table | teledb
trash | trash_table | table | teledb
(2 rows)
\dv "trash".*
List of relations
Schema | Name | Type | Owner
--------+------+------+----------
trash | _3.1 | view | teledb
(1 row)
SELECT org_relname, new_relname, org_nspname, relowner FROM trash.trash_table;
org_relname | new_relname | org_nspname | relowner
-------------+-------------+-------------+----------
test1 | _1 | public | teledb
test2 | _2 | public | teledb
t1 | _3 | public | teledb
(3 rows)
-- matview
CREATE TABLE t2(id int);
CREATE MATERIALIZED VIEW mv1 AS SELECT * FROM t2;
DROP TABLE t2;
ERROR: cannot drop table t2 because other objects depend on it
DETAIL: materialized view mv1 depends on table t2
HINT: Use DROP ... CASCADE to drop the dependent objects too.
DROP TABLE t2 CASCADE;
NOTICE: drop cascades to materialized view mv1
\dt "trash".*
List of relations
Schema | Name | Type | Owner
--------+-------------+-------+----------
trash | _3 | table | teledb
trash | _4 | table | teledb
trash | trash_table | table | teledb
(3 rows)
\dmv "trash".*
List of relations
Schema | Name | Type | Owner
--------+------+-------------------+----------
trash | _3.1 | view | teledb
trash | _4.1 | materialized view | teledb
(2 rows)
SELECT org_relname, new_relname, org_nspname, relowner FROM trash.trash_table;
org_relname | new_relname | org_nspname | relowner
-------------+-------------+-------------+----------
test1 | _1 | public | teledb
test2 | _2 | public | teledb
t1 | _3 | public | teledb
t2 | _4 | public | teledb
(4 rows)
-- index
CREATE TABLE t3(id int);
CREATE INDEX i1 ON t3(id);
DROP TABLE t3;
\dt "trash".*
List of relations
Schema | Name | Type | Owner
--------+-------------+-------+----------
trash | _3 | table | teledb
trash | _4 | table | teledb
trash | _5 | table | teledb
trash | trash_table | table | teledb
(4 rows)
\d+ "trash"._5
Table "trash._5"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
id | integer | | not null | | plain | |
Indexes:
"_5.1" btree (id)
Distribute By: SHARD(id)
Location Nodes: ALL DATANODES
SELECT org_relname, new_relname, org_nspname, relowner FROM trash.trash_table;
org_relname | new_relname | org_nspname | relowner
-------------+-------------+-------------+----------
test1 | _1 | public | teledb
test2 | _2 | public | teledb
t1 | _3 | public | teledb
t2 | _4 | public | teledb
t3 | _5 | public | teledb
(5 rows)
