PostgreSQL に馴染みがなく、アップサートあるいは、SERIAL に困惑している。
MySQL には、AUTO INCREMENT があり、例えば、以下のようなテーブル
CREATE TABLE sample
(
id INT NOT NULL AUTO_INCREMENT,
point INT,
price INT,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1
これと同等な PostgreSQL でのテーブルは、
CREATE TABLE sample
(
id SERIAL NOT NULL,
point INT,
price INT,
CONSTRAINT sample_pkey PRIMARY KEY (id)
)
制約を見る。。
SELECT table_name, constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name='sample';
table_name | constraint_name | constraint_type
sample | sample_pkey | PRIMARY KEY
sample | 2200_16824_1_not_null | CHECK
まず、MySQLの AUTO INCREMENT 同様、PostgreSQL の 動き、
SELECT * FROM sample;
id | point | price
1 | 10 | 100
2 | 20 | 200
(2 行)
ここで、、、INSERT INTO sample (point, price) VALUES (30, 300)
を実行する。MySQL も同じく、PostgreSQL でも、
SELECT * FROM sample;
id | point | price
1 | 10 | 100
2 | 20 | 200
3 | 30 | 300
(3 行)
ここまでは、別に違和感もなく特になにもなく納得。
アップサートを行う！！
まず、存在するレコードに対して、、、
MySQL では、以下のように書く。
INSERT INTO sample (id, `point`, price) VALUES (3, 40, 400)
ON DUPLICATE KEY UPDATE
`point` = VALUES(`point`)
, price = VALUES(price)
PostgreSQL では、以下のように書く
INSERT INTO sample (id, `point`, price) VALUES (3, 40, 400)
ON CONFLICT ON CONSTRAINT sample_pkey
DO UPDATE SET point = 40, price = 400
どちらも、id = 3 のレコードだけが更新される。
実践では、動的ＳＱＬ文生成でこのアップサート文を挿入か更新か処理設計の都合で
実行時にならないと定まらないまま使う。
「アップサート」という本来の目的からすれば、挿入か更新か実行時に判断されることを
期待している。
だから、MySQL では、アップサートが以下のように、制約キー id が NULL の時、、
INSERT INTO sample (id, `point`, price) VALUES (null, 40, 400)
ON DUPLICATE KEY UPDATE
`point` = VALUES(`point`)
, price = VALUES(price)
これは、更新でなく新しいレコードが挿入される。AUTO INCREMENT が効いてくる。
しかし、、、PostgreSQL では、、
INSERT INTO sample (id, `point`, price) VALUES (null, 40, 400)
ON CONFLICT ON CONSTRAINT sample_pkey
DO UPDATE SET point = 40, price = 400
ERROR: 列"id"内のNULL値はNOT NULL制約違反です
エラーになり、新しいレコード挿入にならない！！・・・あれ？上に書いたように、
INSERT INTO sample (point, price) VALUES (30, 300)
が成功する PostgreSQL に AUTO INCREMENT の代わりとされてる SERIAL は、何なの？
PostgreSQL のアップサート文で以下のようにすると成功する。
INSERT INTO sample (id, point, price) VALUES (nextval('sample_id_seq'::regclass), 40, 400)
ON CONFLICT ON CONSTRAINT sample_pkey
DO UPDATE SET point = 40, price = 400
これじゃ、実行するＳＱＬ生成として、 VALUES 文に、null を入れる代わりに、
nextval('sample_id_seq'::regclass) を入れるなんて、変なことをしなくてはならない。
挿入でも更新でも１つのアップサートＳＱＬでという本来の目的に合わない！
実践の処理として考えると既存データを編集した登録のＳＱＬは、制約キーＩＤの値が存在するデータで
ＳＱＬ文にパラメータ渡しで生成するであろうし、
新しいデータである場合、NULL をＳＱＬ文生成に渡すことになる。この実行時にならないと
決定しない時に、アップサートのＳＱＬ文が威力を発揮するはずで、
PostgreSQL のアップサートとされている文は、動的変化に耐えられない。
PostgreSQL より、MySQL の方に軍配が上がる。
そもそも、そんなＤＢの比較なんて無意味なのか。。。
