[PostgreSQL] 如何更新 PostgreSQL 中的 auto increment 欄位值

• 找到 column_default 包含 nextval 的欄位即為 auto increment 欄位

SELECT column_name, column_default FROM information_schema.columns WHERE table_name = "table_name";

• 找到 sequence name
  預設 sequence name 為 table_name_column_name_seq

SELECT pg_get_serial_sequence('table_name', 'column_name');

• 目前的 sequence 值
  

SELECT last_value FROM sequence_name;

• 重設 auto increment 欄位值
  

SELECT SETVAL('sequence_name', (SELECT MAX(column_name) FROM table_name));

ALTER SEQUENCE sequence_name RESTART WITH 1;