auto_increment と serial

MySQL の以下の SQL は成功する

create table test ( a int primary key auto_increment );

insert into test values ( 1 );
insert into test values ( 2 );
insert into test values ( 3 );

insert into test values ( default ); # 最大値 ( 3 ) + 1 = 4 を insert するので OK

けど

PostgreSQL の以下の SQL は失敗する

create table test ( a serial primary key );

insert into test values ( 1 );
insert into test values ( 2 );
insert into test values ( 3 );

insert into test values ( default ); # シーケンスの次の値 = 1 を insert しようとするのでエラー

ERROR: duplicate key violates unique constraint "test_pkey"

ので注意。

PostgreSQL の場合、値を指定して insert しても

insert into test values ( 1 );
insert into test values ( 2 );
insert into test values ( 3 );

シーケンスの次の値は 1 のままなので

insert into test values ( default );

以下参考。

MySQL 4.1 リファレンスマニュアル :: 6.5.3 CREATE TABLE 構文

整数型カラムは追加属性 AUTO_INCREMENT を持つことができる。 インデックス付きの AUTO_INCREMENT カラムに NULL（推奨）または 0 を挿入すると、そのカラムには連続値の次の値が設定される。 通常、これは value+1 になる（value はテーブルに現在格納されているそのカラムの最大値）。 AUTO_INCREMENT は 1 から開始される。

データ型

serialおよびbigserialデータ型は正確にはデータ型ではなく、テーブルの列に一意の識別子を設定する簡便な表記法です。 (他のデータベースでサポートされるAUTO_INCREMENTプロパティに似ています。) 現在の実装では、

CREATE TABLE tablename (
colname SERIAL
);
は以下を指定することと同じです。

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL
);