Node.js + PostgreSQL 環境で複数レコードのバルクインサートを、それも対象テーブルに UNIQUE 制約が存在している場合のバルクインサート方法について調べる機会があったので、ブログにまとめておきます。


【Node.js で PostgreSQL を使う】
まず、Node.js で PostgreSQL クライアント機能を実装する場合、よく使われているのが pg(node-postgres) という npm のライブラリだと思っています：



こんな感じでコネクションプーリングを作って、、

  :
  :
var PG = require( 'pg' );
var pg = new PG.pool( 'postgres://localhost:5432/mydb' );
  :
  :

こんな感じで SQL （以下の例は "INSERT" 処理）を実行し、結果を取得します：

  :
  :
if( pg ){
  var conn = await pg.connect();                                      //. コネクション割当
  if( conn ){
    try{
      var sql = "insert into table1( name, num ) values ( $1, $2 )";  //. SQL
      var query = { text: sql, values: [ "木村", 100 ] );             //. SQL パラメータ
      conn.query( query, function( err, result ){
        if( err ){
          console.log( err );                                         //. SQL 実行時エラー
        }else{
          console.log( result );                                      //. SQL 実行結果
        }
      });
    }catch( e ){
      console.log( e );                                               //. 例外発生
    }finally{
      if( conn ){
        conn.release();                                               //. コネクションのリリース
      }
    }
  }
}
  :
  :

今回もこの pg を使って紹介します。


【Node.js + PostgreSQL でバルクインサートを実装する】
上の例では一回の SQL 実行で１つのレコードをインサートする、という処理の例を紹介しました。複数のレコードをインサートする場合もこれを複数回続けて実行してもよいのですが、レコード数が多い場合に効率よくインサートできると便利です。このように「複数のレコードを１回の処理でまとめて作成する」ことを「バルクインサート」と呼びます。

Node.js + PostgreSQL でバルクインサートを実装する方法もいくつかありますが、今回は node-pg-format という npm ライブラリを使った例を紹介します：



実際に使う場合はバルクインサートデータを配列で用意して、以下のように実装します：

  :
  :
var format = require( 'pg-format' );

var records = [         //. インサートするレコードを配列で用意
  [ "木村", 100 ],
  [ "鈴木", 101 ],
  [ "木村", 102 ]
];

if( pg ){
  var conn = await pg.connect();                                      //. コネクション割当
  if( conn ){
    try{
      var sql = format( "insert into table1( name, num ) values %L", records );  //. SQL
      var query = { text: sql, values: [] );                          //. SQL パラメータ
      conn.query( query, function( err, result ){
        if( err ){
          console.log( err );                                         //. SQL 実行時エラー
        }else{
          console.log( result );                                      //. SQL 実行結果
        }
      });
    }catch( e ){
      console.log( e );                                               //. 例外発生
    }finally{
      if( conn ){
        conn.release();                                               //. コネクションのリリース
      }
    }
  }
}
  :
  :

上例のように、SQL 文全体を pg-format でインスタンス化しています。その際にインサートする複数レコードを配列データとして付与しています（conn.query() 実行時のパラメータは空です）。これで records 配列変数で指定された複数レコードを１回の SQL 実行でまとめてインサートすることができるようになります。


【Node.js + PostgreSQL のバルクインサートと UNIQUE 制約】
ここからがこのブログエントリの本題に入っていきます。バルクインサートの方法は上で紹介しました。問題はこれではうまくいかないケースがあるのでした。具体的には対象テーブルに UNIQUE 制約が付与されていて、この制約にかかるレコードがバルクインサートの一部に含まれる場合です。

具体的に紹介します。例えば今回は table1 という名称のテーブルに複数レコードをバルクインサートする例を紹介していますが、この table1 テーブル作成時に以下のような指定がされていた可能性は排除できません：

create table table1( name varchar(50) default '', num int default 0 );
alter table table1 add unique( name, num );

１行目の "create table ..." はいいとして、問題は２行目です。この制約を与えたことによって name と num の組み合わせは一意(UNIQUE) でないといけない、という条件が付与されました。name は被ってもいいし、num も被ってかまわないのですが、name と num の組み合わせはユニークでないといけない（この条件に反するデータはインサートできない）ようになりました。

このような UNIQUE 制約が存在している前提で、改めて上例を見てみます。この例でバルクインサートしようとしているレコードは、

var records = [         //. インサートするレコードを配列で用意
  [ "木村", 100 ],
  [ "鈴木", 101 ],
  [ "木村", 102 ]
];

という内容でした（配列の１つ目が name 、２つ目が num としてバルクインサートされます）。ここだけを見るとこの例に限っては（name と num の組み合わせは全て異なるので）問題なくバルクインサートできそうです。　・・・が、もしこのバルクインサート実行前に既に { name: "木村", num: 100 }
のデータが記録済みであった場合はどうでしょう？バルクインサートの最初のレコードは UNIQUE 制約にかかってしまいます。つまり実行エラーとなります。

そして更にこの問題をややこしくしている事情があります。それは「バルクインサートが UNIQUE 制約エラーで失敗するのはいいが、その時 UNIQUE 制約にかからなかったレコードはインサートされるべきか？否か？　という問題です。この答はおそらくデータやアプリケーション次第、つまりケース・バイ・ケースです。まとめてインサートされないと意味がないレコードであった場合はまとめてエラー（UNIQUE 制約にかからなかったレコードもインサートされない）という挙動が正しい挙動となり、UNIQUE 制約にかかったレコードはインサートされないが、他のレコードはインサートされるべきデータであった場合はバルクインサートの一部だけが失敗し、残りのレコードは挿入されているべき、ということになります。　

ちなみに上例で紹介したバルクインサート実装の場合は前者の挙動となります。つまり「全てのレコードが制約違反にならなかった場合のみバルクインサートが成功し、１つでも制約違反のレコードがあった場合はそうでないレコードも含めて１つもインサートされずにエラーとなる」という挙動です（そもそも厳密には「バルクインサート」とはそのような挙動および処理を意味する言葉です）。


【Node.js + PostgreSQL のバルクインサートと UNIQUE 制約】
長い前置きでした。ではここまでの背景を理解した上で UNIQUE 制約に違反しているレコードのみ挿入されず、違反していないレコードは正しく挿入されるようなバルクインサート（「バルクインサート」の定義としてはおかしいですけど・・）を実装するにはどのようにすればよいでしょうか？　これが本ブログエントリの肝となる部分です。

これは実際には使っている RDB やライブラリ、プログラミング言語によって異なるようですが、PostgreSQL 9.5 以上で、Node.js + node-postgres を使っている場合であれば以下の方法で実現できるようです：

  :
  :

var records = [         //. インサートするレコードを配列で用意
  [ "木村", 100 ],
  [ "鈴木", 101 ],
  [ "木村", 102 ]
];

if( pg ){
  var conn = await pg.connect();                                      //. コネクション割当
  if( conn ){
    try{
      //. UNIQUE 制約に違反したデータを無視するような SQL を作る
      var sql = "insert into table1( name, num ) ";
      var selects = [];
      for( var i = 0; i < records.length; i ++ ){
        selects.push( "select '" + records[i][0] + "', " + records[i][1] );
      }
      sql += selects.join( " union all" ) + " on conflict ( name, num ) do nothing";

      var query = { text: sql, values: [] );                          //. SQL パラメータ
      conn.query( query, function( err, result ){
        if( err ){
          console.log( err );                                         //. SQL 実行時エラー
        }else{
          console.log( result );                                      //. SQL 実行結果
        }
      });
    }catch( e ){
      console.log( e );                                               //. 例外発生
    }finally{
      if( conn ){
        conn.release();                                               //. コネクションのリリース
      }
    }
  }
}
  :
  :

この例ではプログラミングによって、以下のような SQL 文を生成して実行しています：

insert into table1( name, num )
  select '木村', 100 union all
  select '鈴木', 101 union all
  select '木村', 102
  on conflict ( name, num ) do nothing

厳密にはバルクインサートではないのですが、１回の SQL 実行で複数のレコードをインサートし、"conflict"（UNIQUE 制約違反）が発生した場合は "do nothing"（無視）するよう指定しています。その結果、UNIQUE 制約違反のないレコードのみがまとめてインサートされる、という処理が実現できています。

バルクインサートで１度に挿入するレコード数が莫大になるようなケース（SQL 文自体が膨大で複雑になるケース）には向かないかもしれませんが、自分の手元では一応期待どおりに動くことを確認しました。ちょうどこういう処理を行いたいアプリを作っていて調べたのですが、他の人の参考になれば。


【参照】
https://stackoverflow.com/questions/53722405/how-to-insert-bulk-rows-and-ignore-duplicates-in-postgresql-9-3

タグ ：

#node.js

#pg

#postgres

#postgresql

#unique

#bulk

#insert