將 PostgreSQL 所有 Table 欄位預設值為 Sequence 的現值，改為欄位值的最大值

前言

在「將 Microsoft SQL Server Database 資料轉到 PostgreSQL - Migration Toolkit」一文中，已順利地將資料從 MSSQL 轉到了 PostgreSQL。

而原本是 Identity 欄位，也會改用 PostgreSQL 的 Sequence 來取代，名稱為 [table_name]_[column_name]_seq。

這時新增資料時，會發生 23505: duplicate key value violates unique constraint “pk_[table_name]” 的錯誤

解法

這是因為目前 Sequence 的開始值為 1，所以新增資料時，在取得的資料是 1，就會跟原有的資料表衝突了。

所以需要將 Sequence 的現值改成 Table 欄位的最大值，如下，

select setval('[sequence_name]', (SELECT MAX([column_name]) FROM [table_name]));

但是因為轉檔的資料表很多，總不能一個一個去寫吧~
所以可以把所有使用到 Sequence 的 Table 及 Column 找出來，然後組成 上述的 SQL ，再匯出 CSV 後，整批執行就可以了。

SELECT  format('select setval(''%s'', (SELECT MAX(%s) FROM %s));'
		   , concat(table_name, '_', column_name, '_seq')
		   , column_name,table_name) FROM information_schema.columns 
			WHERE column_default LIKE 'nextval%';

匯出 CSV 後，將第一列 Header 刪掉，並將 “ 移除，就是一條條完整的 SQL ，或是透過 Excel 開啟後 Copy 也可以哦!

參考資料

CREATE SEQUENCE
Sequence Manipulation Functions