On 1 May 2017 at 17:51, Max Wang <mwang@xxxxxxxxxxxxx> wrote: > We have a PostgreSQL database. There are 26 tables and we use serial type as > primary key. We had a insert error as “duplicate key value violates unique > constraint, DETAIL: Key (id)=(1) already exists.” one weeks ago. I checked > and found all tables’ id were reset to 1. Sounds like something that might happen if you'd just bulk loaded the data and didn't set the sequences. If you really did use serial types then you could set all these to the max value of the column which they belong to. The following will give you a list of commands to execute: SELECT 'select setval(''' || c.relname || ''', max(' || quote_ident(a.attname) || ')) from ' || d.refobjid::regclass || ';' FROM pg_depend d INNER JOIN pg_class c ON d.objid = c.oid INNER JOIN pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid WHERE c.relkind = 'S' AND d.refclassid = 1259; You may like to check that returns 26 rows as you expect and verify that all those sequences do need reset before running the command. If you're running Postgres 9.6 and using psql, you can execute the above then execute \gexec which will execute the previous result set as commands. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general