我有下表的数据库架构:
database=# \d person
Table "public.person"
Column | Type | Modifiers
-------------+-----------------------+-----------
person_id | smallint | not null
fname | character varying(20) |
lname | character varying(20) |
eye_color | color_enum |
birth_date | date |
street | character varying(30) |
city | character varying(20) |
state | character varying(20) |
country | character varying(20) |
postal_code | character varying(20) |
我想AUTO_INCREMENT
在一个ALTER
语句中添加我们可以在 MySQL 中执行的方式
ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;
我在 Postgres 中试过这个,但我收到了这个错误:
ALTER TABLE person ALTER COLUMN person_id SERIAL;
ERROR: syntax error at or near "SERIAL"
我已经看到我们可以按以下方式创建一个序列
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
CREATE SEQUENCE test_id_seq OWNED BY test1.id;
ALTER TABLE test ALTER COLUMN id SET DEFAULT nextval('test_id_seq');
UPDATE test1 SET id = nextval('test_id_seq');
但这是太多的样板代码。是否有一行语句可以添加AUTO_INCREMENT
到 Postgres 中的现有列?
Postgres 版本:9.6.16
更新
完成样板代码后,我尝试使用以下查询插入:
INSERT INTO person
(person_id, fname, lname, eye_color, birth_date)
VALUES (null, 'William','Turner', 'BR', '1972-05-27');
ERROR: null value in column "person_id" violates not-null constraint
DETAIL: Failing row contains (null, William, Turner, BR, 1972-05-27, null, null, null, null, null).
是否有一种解决方法可以将空值传递给该列的值来自序列的主键?
回答
能够使用以下错误插入:
INSERT INTO person(person_id, fname, lname, eye_color, birth_date)
VALUES (nextval('person_id_seq'), 'William','Turner', 'BR', '1972-05-27');
如果样板代码太多,那么创建一个函数来完成这项工作:
set not null
如果您知道所有列都已定义为 ,则可以删除该语句NOT NULL
。为了使序列与现有值同步,上面的代码基本上运行:
(更新所有行并可能更改它们的主键值是同步序列的错误方法)。
然后要更改表,您需要做的就是:
您有 2 个选项:
在插入触发器之前,用一些序列设置 person_id 列。
将默认值设置为排序下一个值。示例如下。