Postgres 的 ID 为什么会跳号，以及如何修复导入数据后的「duplicate key」错误

CREATE TABLE orders (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE
);

INSERT INTO orders (email) VALUES ('a@example.com');           -- 取得 1

BEGIN;
INSERT INTO orders (email) VALUES ('b@example.com');           -- 取得 2
ROLLBACK;                                                      -- 2 没了

INSERT INTO orders (email) VALUES ('c@example.com');           -- 取得 3
INSERT INTO orders (email) VALUES ('a@example.com')
  ON CONFLICT (email) DO NOTHING;                              -- 消耗掉 4
INSERT INTO orders (email) VALUES ('a@example.com');           -- 消耗掉 5，失败
INSERT INTO orders (email) VALUES ('d@example.com');           -- 取得 6

SELECT id, email FROM orders ORDER BY id;

 id |     email
----+---------------
  1 | a@example.com
  3 | c@example.com
  6 | d@example.com
(3 rows)

CREATE TABLE events (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  note text
);
INSERT INTO events (note) SELECT 'before crash' FROM generate_series(1, 5);
SELECT last_value, log_cnt FROM events_id_seq;

 last_value | log_cnt
------------+---------
          5 |      28
(1 row)

LOG:  database system was not properly shut down; automatic recovery in progress

INSERT INTO events (note) VALUES ('after crash') RETURNING id;

 id
----
 34
(1 row)

CREATE SEQUENCE ticket_seq CACHE 20;
SELECT nextval('ticket_seq');    -- 会话 1 预留 1..20
\c
SELECT nextval('ticket_seq');    -- 会话 2 预留 21..40

 nextval
---------
       1
(1 row)

You are now connected to database "postgres" as user "postgres".
 nextval
---------
      21
(1 row)

CREATE TABLE customers (
  id   serial PRIMARY KEY,
  name text NOT NULL
);

-- CSV 导入、ORM 种子数据或从另一个数据库复制时所做的事：
COPY customers (id, name) FROM stdin;
1	Ada
2	Grace
3	Edsger
\.

INSERT INTO customers (name) VALUES ('Barbara');

ERROR:  duplicate key value violates unique constraint "customers_pkey"
DETAIL:  Key (id)=(1) already exists.

SELECT pg_get_serial_sequence('customers', 'id');
SELECT setval(pg_get_serial_sequence('customers', 'id'), max(id)) FROM customers;
INSERT INTO customers (name) VALUES ('Barbara') RETURNING id;

 pg_get_serial_sequence
-------------------------
 public.customers_id_seq
(1 row)

 setval
--------
      3
(1 row)

 id
----
  4
(1 row)

CREATE TABLE audit_log (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY);
SELECT setval(pg_get_serial_sequence('audit_log', 'id'), max(id)) FROM audit_log;
SELECT setval(pg_get_serial_sequence('audit_log', 'id'), 0);

 setval
--------

(1 row)

ERROR:  setval: value 0 is out of bounds for sequence "audit_log_id_seq" (1..9223372036854775807)

SELECT format(
         'SELECT setval(%1$L, GREATEST('
         'COALESCE((SELECT max(%2$I) FROM %3$I.%4$I), 0) + 1, '
         '(SELECT last_value + is_called::int FROM %1$s)), false);',
         s.oid::regclass, a.attname, n.nspname, t.relname)
FROM pg_class s
JOIN pg_depend d    ON d.classid = 'pg_class'::regclass
                   AND d.objid = s.oid
                   AND d.refclassid = 'pg_class'::regclass
                   AND d.deptype IN ('a', 'i')   -- serial / identity
JOIN pg_class t     ON t.oid = d.refobjid
JOIN pg_namespace n ON n.oid = t.relnamespace
JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = d.refobjsubid
WHERE s.relkind = 'S'
ORDER BY 1
\gexec

SELECT setval('audit_log_id_seq', GREATEST(COALESCE((SELECT max(id) FROM public.audit_log), 0) + 1, (SELECT last_value + is_called::int FROM audit_log_id_seq)), false);
 setval
--------
      1
(1 row)

SELECT setval('customers_id_seq', GREATEST(COALESCE((SELECT max(id) FROM public.customers), 0) + 1, (SELECT last_value + is_called::int FROM customers_id_seq)), false);
 setval
--------
      5
(1 row)

CREATE TABLE clicks (id serial PRIMARY KEY);
SELECT setval('clicks_id_seq', 2147483646);     -- 为演示而快进
INSERT INTO clicks DEFAULT VALUES RETURNING id;
INSERT INTO clicks DEFAULT VALUES RETURNING id;

     id
------------
 2147483647
(1 row)

ERROR:  nextval: reached maximum value of sequence "clicks_id_seq" (2147483647)

SELECT schemaname, sequencename, data_type, last_value, max_value,
       round(100.0 * last_value / max_value, 2) AS pct_used
FROM pg_sequences
ORDER BY pct_used DESC NULLS LAST, sequencename
LIMIT 3;

 schemaname |   sequencename   | data_type | last_value |      max_value      | pct_used
------------+------------------+-----------+------------+---------------------+----------
 public     | clicks_id_seq    | integer   | 2147483647 |          2147483647 |   100.00
 public     | ticket_seq       | bigint    |         40 | 9223372036854775807 |     0.00
 public     | audit_log_id_seq | bigint    |            | 9223372036854775807 |
(3 rows)

ALTER SEQUENCE clicks_id_seq AS bigint;
INSERT INTO clicks DEFAULT VALUES RETURNING id;

ERROR:  integer out of range

CREATE TABLE invoice_counters (
  year    int PRIMARY KEY,
  last_no int NOT NULL
);
INSERT INTO invoice_counters VALUES (2026, 0);

CREATE TABLE invoices (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  year   int NOT NULL,
  no     int NOT NULL,
  amount numeric NOT NULL,
  UNIQUE (year, no)
);

BEGIN;
WITH n AS (
  UPDATE invoice_counters SET last_no = last_no + 1
  WHERE year = 2026
  RETURNING last_no
)
INSERT INTO invoices (year, no, amount)
SELECT 2026, last_no, 120 FROM n
RETURNING id, year, no;
ROLLBACK;

 id | year | no
----+------+----
  2 | 2026 |  1
(1 row)

 last_no
---------
       3
(1 row)

UPDATE 1
Time: 3943.462 ms (00:03.943)

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.