ほんじゃら堂

めんどくさい仕事をラクにする作業自動化レシピ集

PostgreSQLでのUPSERT(INSERT or UPDATE)処理を検証する

f:id:piro_suke:20161202005524j:plain

PostgreSQLでUPSERT(データがあればUPDATEしてなければINSERT)する

方法について調べたところ、いくつか書き方があるようだ。

UPSERT処理で気になるのは同時実行された際に

重複登録されたり重複エラーが発生しないかどうかなので、

その観点で下記の4パターンの方法を検証してみる。

  1. UPDATEしてNOT FOUNDならINSERT
  2. CTEでUPDATEしてNOT EXISTSならINSERT
  3. INSERT ON CONFLICT DO UPDATE
  4. INSERTしてUNIQUE_VIOLATION EXCEPTIONならUPDATE
続きを読む

PostgreSQLでpg_bigmを使って中間一致like検索を高速化する

f:id:piro_suke:20161119021025j:plain

昨日書いた下記記事の続き。

blog.honjala.net

全角半角大文字小文字ひらがなカタカナを区別せず高速検索できるようには

なったのだけど、肝心のlike検索でインデックスが使えず

遅いままだったので、pg_bigmを導入してこれを解決する。

pg_bigmは簡単に言うと日本語の中間一致like検索でインデックスを

使えるようにしてくれるPostgreSQL用モジュール。

本家サイトはこちら:

pg_bigm

続きを読む

PostgreSQLで全角半角大文字小文字ひらがなカタカナを区別せず検索したい!というよくあるわがままに応える

f:id:piro_suke:20161118011707j:plain

したいしたい!絶対したい!と駄々をこねられたので調査してみた。

こういった区別なし検索を実装する方法としてパッと思いつくのは、

あらかじめ検索対象となるカラムの検索用カラムを用意して、

データ変更時にトリガーで

元カラムの内容を半角小文字英数字カタカナに変換したデータが入るようにしておき、

検索時はその検索用カラムを使用する、という方法。

これはめんどくさそうだ。

続きを読む

PostgreSQLでテスト用組み合わせパターンデータを一括生成する

f:id:piro_suke:20161110013537j:plain

先日書いたgenerate_series関数を使用したテストデータ生成の続き。

blog.honjala.net

どうせテストデータを作成するなら、

連番だけでなく、必要なパターンの組み合わせデータを生成したい。

ちょっと前にPythonやClojureを使った組み合わせデータ作成のスクリプトを

作成した時は専用のライブラリを使用したのだけど、

blog.honjala.net

PostgreSQLのgenerate_series関数とjoinを組み合わせたら

ぐっと簡単に実現できた。

続きを読む

JSONB型カラムでPostgreSQLをNoSQL風にスキーマレスに使う方法

f:id:piro_suke:20161104012901j:plain

PostgreSQL9.2以降のバージョンではjson型/jsonb型カラムが使える。

ちょっと特殊な記法を使うけど、

json/jsonbデータ内のキーをDBのカラムと同様に使えるようになるので、

スキーマレスDBのように同じテーブル内のレコードで

異なるデータ構成を持たせることができるようになる。

役に立ちそうなので触ってみた。

続きを読む

PostgreSQLのgenerate_series関数でテスト用データを作成する

f:id:piro_suke:20161102010901j:plain

これまでテスト用にたくさんデータを作成する時は、

PythonやらNode.jsやらでプログラムを書いてた。

blog.honjala.net

PostgreSQLならgenerate_seriesという連続値生成関数を利用することで

SQLだけで手軽にデータ生成できる、ということを発見したので、

今回は上記の記事と同じようなテストデータをSQLで作成してみる。

環境はPostgreSQL 9.5。

下記のようなテーブルを作成する。

drop table if exists bulk_test_items;
create table bulk_test_items(
  id serial not null,
  item_cd varchar(100),
  name varchar(100),
  created timestamp default current_timestamp,
  primary key(id)
);

そして、generate_seriesとinsert-selectを組み合わせたデータ生成SQLを作成する。

insert into bulk_test_items(item_cd, name, created)
select
  to_char(i, 'FM0000000000'),
  format('テスト商品%s', i), 
  clock_timestamp()
from
  generate_series(1, 10) as i
;

これで下記のようなデータが生成される:

 id |   item_cd   |     name     |          created
----+-------------+--------------+----------------------------
  1 |  0000000001 | テスト商品1  | 2016-11-02 00:42:30.138823
  2 |  0000000002 | テスト商品2  | 2016-11-02 00:42:30.147692
  3 |  0000000003 | テスト商品3  | 2016-11-02 00:42:30.14774
  4 |  0000000004 | テスト商品4  | 2016-11-02 00:42:30.147766
  5 |  0000000005 | テスト商品5  | 2016-11-02 00:42:30.147788
  6 |  0000000006 | テスト商品6  | 2016-11-02 00:42:30.14781
  7 |  0000000007 | テスト商品7  | 2016-11-02 00:42:30.147832
  8 |  0000000008 | テスト商品8  | 2016-11-02 00:42:30.147855
  9 |  0000000009 | テスト商品9  | 2016-11-02 00:42:30.147878
 10 |  0000000010 | テスト商品10 | 2016-11-02 00:42:30.147901
(10 行)

なんて簡単なんでしょう。

generate_seriesに渡すパラメータを変更すれば数万件のデータも作成できる。

9.24. 集合を返す関数

おわり

いいもの発見した、と思ってたら

既にもっと色々なパターンを紹介してくれている解説記事が公開されていた。

lets.postgresql.jp

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)

PostgresqlのSELECT句での関数呼び出し順序を確認した

f:id:piro_suke:20161029024850j:plain

チーム内の雑談で、

Delete-Insert処理を1つのSQL文で行うとしたら、

SELECT文のSELECT句でDELETE用SPとINSERT用SPを呼び出す方法もあるよね、

SELECT句って記述順に実行してくれるのかな?

みたいな話になったので、検証してみた。

続きを読む