使用Postgres，我试图用AUTO_INCREMENT在SQL中自动为主键编号.然而，它给了我一个错误.
CREATE TABLE Staff (
ID INTEGER NOT NULL AUTO_INCREMENT,
Name VARCHAR(40) NOT NULL,
PRIMARY KEY (ID)
);
错误是:
********** Error ********** ERROR: syntax error at or near "AUTO_INCREMENT" SQL state: 42601 Character: 63
知道为什么吗？
serial列(见下文)保持不变.但是考虑101栏.Postgres 10实现了这一标准SQL功能.
Basic syntax and info in the manual for CREATE TABLE.
Detailed explanation in this blog entry of its primary author Peter Eisentraut.
IDENTITY columnCREATE TABLE staff (
staff_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
, staff text NOT NULL
);IDENTITY column to existing table表中可能有行，也可能没有行.
ALTER TABLE staff ADD COLUMN staff_id int GENERATED BY DEFAULT AS IDENTITY;
要同时使其成为PK(表还不能有PK):
ALTER TABLE staff ADD COLUMN staff_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY;
相关的:
serial with IDENTITY column见:
(Or any version, really.)
Use the serial pseudo data type instead:
CREATE TABLE staff (
staff_id serial PRIMARY KEY,
, staff text NOT NULL
);它会自动创建并附着序列对象，并将序列中的DEFAULT设置为nextval().它能满足你的所有需求.
我在例子中使用了lower case identifiers.让你在博士后的生活更轻松.