年明け1/7にPostgreSQL 9.5がリリースされた。
仮運用してみたが特に問題なく使えている。
ここでは9.5の目玉の一つ、UPSERTについても検証する。
Contents
PostgreSQL 9.5インストール
パッケージでインストール
1 2 3 4 5 6 | rpm -i http://yum.postgresql.org/9.5/redhat/rhel-6-x86_64/pgdg-centos95-9.5-2.noarch.rpm yum -y install postgresql95 postgresql95-contrib postgresql95-devel postgresql95-libs postgresql95-server su - postgres /usr/pgsql-9.5/bin/initdb --no-locale --encoding=UTF8 exit service postgresql-9.5 start |
※Windows版は
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
からインストーラをダウンロードするのが良いだろう。
初期設定
postgresユーザのパスワード設定
1 2 3 | sudo -u postgres psql alter user postgres with password 'パスワード'; \q |
リモート接続する場合の設定
別なマシンから接続する場合はpostgresql.confを設定する。
1 2 | cd ~postgres/9.5/data vi postgresql.conf |
listen_addressesの#を外し、*に変更する。
57 58 59 60 61 62 63 | # - Connection Settings - listen_addresses = '*' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart) #port = 5432 # (change requires restart) |
md5で認証する場合の設定
OSで認証されたログインユーザで接続する場合はtrustのままで良いが、アプリケーションから接続するなどID/パスワードで接続する場合としてここでは全てmd5で設定する事にする。
また、LAN内(以下の例では192.168.1.1～192.168.1.254)の認証も受け付けるよう設定する。
1 | vi pg_hba.conf |
83 84 85 86 87 | # "local" is for Unix domain socket connections only local all all md5 # IPv4 local connections: host all all 127.0.0.1/32 md5 host all all 192.168.1.1/24 md5 |
再起動時の設定
1 | chkconfig postgresql-9.5 on |
UPSERTの実行
1 2 | psql -U postgres ユーザ postgres のパスワード: |
2回目のINSERTでは重複するのでUPDATEが、3回目のINSERTでは重複しないのでINSERTが実行されているのが確認できる。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | CREATE TABLE distributors ( did INTEGER PRIMARY KEY, dname TEXT ); INSERT INTO distributors VALUES (5, 'Gizmo Transglobal'); SELECT * FROM distributors; did | dname -----+------------------- 5 | Gizmo Transglobal (1 行) INSERT INTO distributors VALUES (5, 'Associated Computing, Inc') ON CONFLICT (did) DO UPDATE SET dname = 'Associated Computing, Inc'; SELECT * FROM distributors; did | dname -----+--------------------------- 5 | Associated Computing, Inc (1 行) INSERT INTO distributors VALUES (7, 'Redline GmbH') ON CONFLICT (did) DO UPDATE SET dname = 'Redline GmbH'; SELECT * FROM distributors; did | dname -----+--------------------------- 5 | Associated Computing, Inc 7 | Redline GmbH |
なお、CONFLICTを検知するカラムにPRIMARY KEYまたはUNIQUE KEYが張ってないと
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
というエラーになる。
また、DO UPDATEではなく、DO NOTHINGも使える。UPSERTの代替を実現する以外にその様な状況になった事がないのでピンと来ないが、INSERTで重複した時も正常系として続行したい場合に使うのだろう。