網頁

2021/11/24

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 重設自動遞增序列遞增值」。


沒有留言:

張貼留言