テーブルのレコード数とsequenceを合わせる in PostgreSQL

開発環境と本番環境のデータを行ったり来たりして、テーブルのレコード数とsequenceの値がずれてinsert時にエラーが起こるのはあるある。いやなくせ。

というわけで両者の値を合わせるSQLを書いた。本当はSQLのみで解決したかったけど、あんまりやらないことなので出力されたSQLを実行するという形式にした。

--
-- 値の比較用の一時テーブルを作る。一時テーブルなので接続を切断したら消える
--
CREATE TEMPORARY TABLE all_sequences as
SELECT t.oid::regclass AS table_name,
       a.attname AS column_name,
       s.relname AS sequence_name
FROM pg_class AS t
   JOIN pg_attribute AS a
      ON a.attrelid = t.oid
   JOIN pg_depend AS d
      ON d.refobjid = t.oid
         AND d.refobjsubid = a.attnum
   JOIN pg_class AS s
      ON s.oid = d.objid
WHERE d.classid = 'pg_catalog.pg_class'::regclass
  AND d.refclassid = 'pg_catalog.pg_class'::regclass
  AND d.deptype IN ('i', 'a')
  AND t.relkind IN ('r', 'P')
  AND s.relkind = 'S';

--
-- 値を入れる列を足す
--
ALTER TABLE all_sequences ADD COLUMN table_count integer default 0;
ALTER TABLE all_sequences ADD COLUMN sequence_value integer default 0;

--
-- テーブルのseq最大値を取得する。SQLの実行結果をSQLで流し込む
--
SELECT 'UPDATE all_sequences SET table_count = (SELECT MAX(' || column_name || ') FROM ' || table_name || ') WHERE table_name = ''' || table_name || '''::regclass;' from all_sequences;

--
-- シーケンスの現在値を取得する。SQLの実行結果をSQLで流し込む
--
SELECT 'UPDATE all_sequences SET sequence_value = (SELECT last_value FROM ' || sequence_name || ') WHERE table_name = ''' || table_name || '''::regclass;' from all_sequences;

--
-- 値が違うものを確認
--
select *, CASE WHEN table_count > sequence_value THEN 'SEQVAL_IS_SMALLER_THAN_SEQMAX' END as equals from all_sequences;

--
-- シーケンスの現在値を実態に合わせる。SQLの実行結果をSQLで流し込む
--
SELECT 'SELECT setval(''' || sequence_name || ''', ' || table_count + 1 || ', false);' from all_sequences where table_count > sequence_value;