On 1 May 2017 at 17:51, Max Wang <mwang@1080agile.com> 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
