如何在postgres表"testing_thing"中将"nextval('testing_thing_thing_id_seq'::regclass)"声明为列"thing_id"的默认值？

Question

在我的postgres db中有一个名为testing_thing的表，我可以看到(通过在psql提示符中运行d testing_thing)它被定义为

Table "public.testing_thing"
Column    |       Type        | Collation | Nullable |                       Default                       
--------------+-------------------+-----------+----------+-----------------------------------------------------
thing_id   | integer           |           | not null | nextval('testing_thing_thing_id_seq'::regclass)
thing_num  | smallint          |           | not null | 0
thing_desc | character varying |           | not null | 
Indexes:
"testing_thing_pk" PRIMARY KEY, btree (thing_num)

我想删除它并重新创建它，但是我不知道如何复制

nextval('testing_thing_thing_id_seq'::regclass)

thing_id列部分

这是我用来创建表的查询:

CREATE TABLE testing_thing(
thing_id integer NOT NULL, --what else should I put here?
thing_num smallint NOT NULL PRIMARY KEY DEFAULT 0,
thing_desc varchar(100) NOT NULL
);

它缺少什么?

Answer 1

将DEFAULT添加到要增加的列并调用nextval():

CREATE SEQUENCE testing_thing_thing_id_seq START WITH 1;
CREATE TABLE testing_thing(
thing_id integer NOT NULL DEFAULT nextval('testing_thing_thing_id_seq'),
thing_num smallint NOT NULL PRIMARY KEY DEFAULT 0,
thing_desc varchar(100) NOT NULL
);

边注请记住，将序列附加到列上并不能防止用户手动地用随机数据填充它，这可能会对主键造成非常严重的问题。如果你想要克服它，并且不一定需要有一个序列，考虑创建一个标识列，例如

CREATE TABLE testing_thing(
thing_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
thing_num smallint NOT NULL PRIMARY KEY DEFAULT 0,
thing_desc varchar(100) NOT NULL
);

Demo:db<>fiddle