PostgreSQL学习|drop table会删除sequence吗？

一、create sequence owned by table.column

1、创建表及sequence

postgres=# create table test_sky (id int,name varchar(10));CREATE TABLEpostgres=# create sequence seq_sky owned by test_sky.id;CREATE SEQUENCE

2、查看sequence

postgres=# \ds                List of relations Schema |       Name       |   Type   |  Owner   --------+------------------+----------+---------- public | id_sec           | sequence | postgres public | seq_sky          | sequence | postgres public |  postgres=# \d seq_sky                          Sequence "public.seq_sky"  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache --------+-------+---------+---------------------+-----------+---------+------- bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1Owned by: public.test_sky.id

3、drop table

postgres=# drop table test_sky;DROP TABLEpostgres=# \ds                List of relations Schema |       Name       |   Type   |  Owner   --------+------------------+----------+---------- public | id_sec           | sequence | postgres public | seq_test1_id_seq | sequence | postgres(2 rows)

可以看到创建的seq_sky被删除了。

二、创建表设置列默认值为nextval(sequence)

1、创建表及sequence

postgres=# create sequence seq_sky;CREATE SEQUENCEpostgres=# create table test_sky (id int,name varchar(10));CREATE TABLEpostgres=# ALTER TABLE test_skypostgres-# ALTER COLUMN id SET DEFAULT nextval('seq_sky');​ALTER TABLEpostgres=# postgres=# \d test_sky                               Table "public.test_sky" Column |         Type          | Collation | Nullable |           Default            --------+-----------------------+-----------+----------+------------------------------ id     | integer               |           |          | nextval('seq_sky'::regclass) name   | character varying(10) |           |          |  

2、drop sequence

postgres=# drop sequence seq_sky;ERROR:  cannot drop sequence seq_sky because other objects depend on itDETAIL:  default value for column id of table test_sky depends on sequence seq_skyHINT:  Use DROP ... CASCADE to drop the dependent objects too.

由于sequence存在依赖，无法被删除

3、drop table/drop table cascade

postgres=# drop table test_sky;DROP TABLEpostgres=# \ds                List of relations Schema |       Name       |   Type   |  Owner   --------+------------------+----------+---------- public | id_sec           | sequence | postgres public | seq_sky          | sequence | postgres public | seq_test1_id_seq | sequence | postgres(3 rows)

postgres=# drop table test_sky cascade;DROP TABLEpostgres=# \ds                List of relations Schema |       Name       |   Type   |  Owner   --------+------------------+----------+---------- public | id_sec           | sequence | postgres public | seq_sky          | sequence | postgres public | seq_test1_id_seq | sequence | postgres(3 rows)

可以看到均未删除sequence。

三、总结

综上所述，只有create sequence owned by table.column时，drop table会将涉及的sequence删除，否则仅是在列中引用，sequence不会被删除。

查阅官方文档进一步确认：

• OWNED BY table_name.column_name
OWNED BY NONE

• The OWNED BY option causes the sequence to be associated with a specific table column, such that if that column (or its whole table) is dropped, the sequence will be automatically dropped as well. The specified table must have the same owner and be in the same schema as the sequence. OWNED BY NONE, the default, specifies that there is no such association.

参考链接：

​https://www.postgresql.org/docs/current/sql-createsequence.html