PostgreSQL 自動遞增欄位 auto increment column

PostgreSQL建立資料表時可使用SERIAL建立自動遞增欄位。

自動遞增欄位在新增資料時會自動增加數值，通常連續整數序列，也就是1,2,3,4,5,...。

下面建立一個employee資料表並設定id為自動遞增欄位。

CREATE TABLE employee (
   id SERIAL PRIMARY KEY,
   name VARCHAR(60) UNIQUE NOT NULL
);

測試新增2筆資料如下。

INSERT INTO "employee" ("name") VALUES ('john');
INSERT INTO "employee" ("name") VALUES ('mary');

查詢結果可看到id的數值順序遞增。

 id | name
----+-------
  1 | john
  2 | mary

然而SERIAL其實是語法糖，同等以下語法。相當於先建立一個名稱為employee_id_seq的自動增量sequence generator（序號產產生器），然後使用nextval()取得sequence的下一個遞增值做為欄位的預設值。最後設定sequence的owner為指定欄位，則當欄位或資料表刪除時會一併刪除所屬的sequence。

CREATE SEQUENCE employee_id_seq AS integer;
CREATE TABLE employee (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('employee_id_seq'),
   name varchar(60) UNIQUE NOT NULL
);
ALTER SEQUENCE employee_id_seq OWNED BY employee.id;

查詢pg_sequences來查看已建立的sequences。

SELECT * FROM pg_sequences;

 schemaname |  sequencename   | sequenceowner | data_type | start_value | min_value | max_value  | increment_by | cycle | cache_size | last_value
------------+-----------------+---------------+-----------+-------------+-----------+------------+--------------+-------+------------+------------
 public     | employee_id_seq | matt          | int       |           1 |         1 | 2147483647 |            1 | f     |          1 |          2

有以下3種自動增量，長度範圍不同。

名稱	佔用空間	敘述	範圍
smallserial	2 bytes	small autoincrementing integer	1 to 32767
serial	4 bytes	autoincrementing integer	1 to 2147483647
bigserial	8 bytes	large autoincrementing integer	1 to 9223372036854775807

重設sequence參考「PostgreSQL 重設自動遞增序列遞增值」。

• CREATE SEQUENCE