淺談Postgresql的Auto Increment機制

先比較常用的MySQL，在PK欄位指定AUTO_INCREMENT，MySQL會幫忙產生Primary Key的流水號，然後填到id欄位裡面

CREATE TABLE foo (
     id    BIGINT       AUTO_INCREMENT,
     bar   VARCHAR(100) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=INNODB;

而PostgreSQL是透過獨立的sequence，產生Primary Key的流水號，

CREATE SEQUENCE foo_foo_id_seq;

CREATE TABLE foo (
    foo_id regclass NOT NULL DEFAULT nextval(('foo_foo_id_seq'::text)::regclass), 
    CONSTRAINT pmky PRIMARY KEY (foo_id)
);

也因為PostgreSQL是透過另外的sequence去產生流水號，有時候會有一些問題出現，接下來要探討一下如何解決這些問題

1. ERROR: relation 'foo_foo_id_seq' does not exists

-- 這個問題在於sequence的產生沒有成功，或者忘記做了這件事情
-- create完之後別忘記要設定給特定Primary Key當做它的流水號哦
CREATE SEQUENCE foo_foo_id_seq;

2. currval of sequence "foo_foo_id_seq" is not yet defined
首先檢查sequence是否存在

# 進入PostgreSQL Command Line
psql

# 列出所有sequence
\ds;
#                      關聯列表
#  架構模式 |        名稱          |  型別  | 擁有者
# ----------+---------------------+--------+------------
#  public   | foo_foo_id_seq      | 序列數 | foobar
#  public   | bar_bar_id_seq      | 序列數 | foobar

確定foo_foo_id_seq存在後，接著就可以去設定它目前的值

select setval('foo_foo_id_seq', 1);

設定成功之後，可以再試著用currval函式去拿foo_foo_id_seq目前的值

select currval('foo_foo_id_seq');
-- currval
-- ---------
--       1
-- (1 筆資料列)

試試看這個sequence能不能正常遞增

select nextval('foo_foo_id_seq');
-- nextval
-- ---------
--       2
-- (1 筆資料列)

大功告成，PostgreSQL的Auto Increment雖然比起MySQL稍嫌麻煩了點，
但對於sequence的值的操作比較有彈性，這樣走過之後，對MySQL跟PostgreSQL就有更進一步的了解

Reference: Diego Benna, wuhahala