Postgresql 简明教程

PostgreSQL - AUTO INCREMENT

PostgreSQL 具有数据类型 smallserial、serial 和 bigserial；这些不是真正的类型，而仅仅是创建唯一标识符列的记号便利。这些类似于某些其他数据库支持的 AUTO_INCREMENT 属性。

PostgreSQL has the data types smallserial, serial and bigserial; these are not true types, but merely a notational convenience for creating unique identifier columns. These are similar to AUTO_INCREMENT property supported by some other databases.

如果您希望序列列具有唯一约束或成为主键，现在必须指定它，就像任何其他数据类型一样。

If you wish a serial column to have a unique constraint or be a primary key, it must now be specified, just like any other data type.

类型名称 serial 创建一个整数列。类型名称 bigserial 创建一个 bigint 列。如果您预计在表的生命周期内使用超过 231 个标识符，则应该使用 bigserial。类型名称 smallserial 创建一个 smallint 列。

The type name serial creates an integer columns. The type name bigserial creates a bigint column. bigserial should be used if you anticipate the use of more than 231 identifiers over the lifetime of the table. The type name smallserial creates a smallint column.

Syntax

SERIAL 数据类型的基本用法如下 −

The basic usage of SERIAL dataype is as follows −

CREATE TABLE tablename (
   colname SERIAL
);

Example

将 COMPANY 表视为如下创建的：

Consider the COMPANY table to be created as follows −

testdb=# CREATE TABLE COMPANY(
   ID  SERIAL PRIMARY KEY,
   NAME           TEXT      NOT NULL,
   AGE            INT       NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

现在，向 COMPANY 表中插入以下记录：

Now, insert the following records into table COMPANY −

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Allen', 25, 'Texas', 15000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Teddy', 23, 'Norway', 20000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Mark', 25, 'Rich-Mond ', 65000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'David', 27, 'Texas', 85000.00 );


INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Kim', 22, 'South-Hall', 45000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'James', 24, 'Houston', 10000.00 );

这将在表 COMPANY 中插入七个元组，而 COMPANY 将具有以下记录 −

This will insert seven tuples into the table COMPANY and COMPANY will have the following records −

 id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000