古いバージョンのPostgreSQLでupsertする

　PostgreSQLでデータベースにレコードを登録する際に、すでに同じキー（制約）を持っているレコードがある場合はUPDATEクエリを、ない場合はINSERTクエリを発行する、いわゆるUPSERT的なクエリが必要な時がある。PostgreSQL9.5以降のバージョンであるなら、ON CONFLICT句を使って下記のようなクエリを発行すれば良い。

INSERT INTO one_table VALUES (1, 'value1') 
ON CONFLICT ON CONSTRAINT constraint_name 
DO UPDATE SET col='value2';

　つまり、例えばプライマリキーのカラム名が id で制約名が constraint_name の空テーブルがあったとして、これに上記のクエリを発行すると、まずINSERTが行われて id=1,col=value1 のレコードが登録される。もう一度同じクエリを発行すると、今度はUPDATEが行われて id=1,col=value2 となる。
　ちなみにUPDATEをしないで、同一キーがない時だけINSERTするようなクエリは下記のようになる。

INSERT INTO one_table VALUES (1, 'value3') 
ON CONFLICT ON CONSTRAINT constraint_name 
DO NOTHING;

　ま、これは良いんです。今回はこの方法が使えない場合なのよね。
　というのも、私が現在保守しているとあるサービスのPostgreSQLのバージョンが9.0系だったのだ。もちろん ON CONFLICTは使えまへん。そんな時の代替策を備忘録として残しておこう（ほぼ自分用）。

# \d one_table
          Table "public.one_table"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | not null
 col    | character varying(10) | 
Indexes:
    "constraint_name" PRIMARY KEY, btree (id)

# SELECT * FROM one_table;
 id |  col   
----+--------
  1 | value1
(1 row)

　上記のようなテーブルに対して非ON CONFLICTなクエリでUPSERTをしてみる。

# UPDATE one_table SET col='add new' WHERE id = 2;
UPDATE 0
# INSERT INTO one_table (id, col) SELECT 2, 'add new' WHERE NOT EXISTS (SELECT 1 FROM one_table WHERE id = 2);
INSERT 0 1
# SELECT * FROM one_table;
 id |   col   
----+---------
  1 | value1
  2 | add new
(2 rows)

# UPDATE one_table SET col='upserted' WHERE id = 2;
UPDATE 1
# INSERT INTO one_table (id, col) SELECT 2, 'upserted' WHERE NOT EXISTS (SELECT 1 FROM one_table WHERE id = 2);
INSERT 0 0
# SELECT * FROM one_table;
 id |   col    
----+----------
  1 | value1
  2 | upserted
(2 rows)

　やってることを解説すると、id=2のレコードをUPDATEしようとして対象レコードがないから何もせずに完了し、直後にid=2のレコードが存在しなければの条件でINSERTして成功。普通にUPDATEしてからINSERTするという地味な２連コンボなクエリですな。
　２回目のコンボでは、id=2のレコードをUPDATEして成功し、直後のINSERTはすでにid=2のレコードがあるので、条件にマッチせずに完了という流れ。
　まぁ、UPDATEを伴わないDO NOTHINGなクエリでよければ、直前のUPDATEが不要になるので、ワンクエリで事足りるのでややスマートになるが、UPSERTやろうとすると毎回２連コンボになるので、コストが高いなぁ…。でも、仕方ない。

関数化して少しでも使いやすくする

　毎度２連コンボクエリを発行するのはダルイので、ストアドファンクションとして関数化してしまえば、だいぶ使いやすくなる。まぁ、クエリコストは変わらないので、気休めでしかないのだが。

DROP FUNCTION IF EXISTS upsert(INTEGER, TEXT);
CREATE OR REPLACE FUNCTION upsert(key INTEGER, value TEXT) 
RETURNS VOID AS $$
  BEGIN
    UPDATE one_table SET col = value WHERE id = key;
    INSERT INTO one_table (id, col) SELECT key, value WHERE NOT EXISTS (SELECT 1 FROM one_table WHERE id = key);
  END;
$$ LANGUAGE plpgsql;

　──という風に、関数を作成する。最初のDROP FUNCTIONはおまじないみたいなものだけど、関数の処理を更新する時には必要。
　もっと汎用化するならテーブル名も引数で渡したいが、それをやろうとすると結構手間がかかるので、今回は割愛。
　早速、使ってみる。

# SELECT upsert(3, 'new');
 upsert 
--------

(1 row)

# SELECT * FROM one_table;
 id |   col    
----+----------
  1 | value1
  2 | upserted
  3 | new
(3 rows)

# SELECT upsert(3, 'new2');
 upsert 
--------

(1 row)

# SELECT * FROM one_table;
 id |   col    
----+----------
  1 | value1
  2 | upserted
  3 | new2
(3 rows)

　だいぶ楽にUPSERTできるようになった。

　ま、PostgreSQLのバージョンアップをするのが本筋ではあるんだよねw