自动增量表列 [英] Auto increment table column

问题描述

使用Postgres，我试图使用AUTO_INCREMENT在SQL中自动为主键编号.但是，这给了我一个错误.

Using Postgres, I'm trying to use AUTO_INCREMENT to number my primary key automatically in SQL. However, it gives me an error.

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

知道为什么吗?

推荐答案

Postgres 10或更高版本

serial列(请参见下文)保持不变.但是考虑一个 IDENTITY 列. Postgres 10实现了此标准SQL功能.

Postgres 10 or later

serial columns (see below) remain unchanged. But consider an IDENTITY column. Postgres 10 implements this standard-SQL feature.

CREATE TABLE staff (
   staff_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
 , staff    text NOT NULL
);

CREATE TABLE的手册中的基本语法和信息.
其主要作者Peter Eisentraut的博客条目中的详细说明.

Basic syntax and info in the manual for CREATE TABLE.
Detailed explanation in this blog entry of its primary author Peter Eisentraut.

要 添加 到现有表(是否填充行)中的IDENTITY列:

ALTER TABLE staff ADD COLUMN staff_id int GENERATED BY DEFAULT AS IDENTITY;

要同时使它成为PK(表尚不能拥有PK):

To also make it the PK at the same time (table can't have a PK yet):

ALTER TABLE staff ADD COLUMN staff_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY;

早期版本中存在一个错误，可能会导致错误消息，例如:

There was a bug in early versions, that could lead to an error message like:

ERROR: column "staff_id" contains null values

此问题已在Postgres 10.2中修复.详细信息:

This was fixed with Postgres 10.2. Details:

• 使用 serial strong>伪数据类型代替:

  CREATE TABLE staff (
     staff_id serial PRIMARY KEY,
   , staff    text NOT NULL
  );

  它会自动创建并附加序列对象，并将序列中的DEFAULT设置为nextval().它可以满足您的所有需求.

  It creates and attaches the sequence object automatically and sets the DEFAULT to nextval() from the sequence. It does all you need.

  我也只使用小写字母标识符.使Postgres的生活更轻松.

  I also use just lower case identifiers in my example. Makes your life with Postgres easier.

  最好使用描述性列名. "id"作为名称是一种反模式，已被某些中间件使用，但几乎没有描述性.与名称"相似.

  And better use descriptive column names. "id" as name is an anti-pattern, used by some middle-ware, but hardly descriptive. Similar with "name".

  这篇关于自动增量表列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！