Re: all serial type was changed to 1

Hi David,

Thanks for suggestion. We use PostgreSQL 9.5. And we did not import or bulk loaded data to these tables.

Regards,
Max

-----Original Message-----
From: David Rowley [mailto:david(dot)rowley(at)2ndquadrant(dot)com]
Sent: Monday, 1 May 2017 11:05 PM
To: Max Wang <mwang(at)1080agile(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] all serial type was changed to 1

On 1 May 2017 at 17:51, Max Wang <mwang(at)1080agile(dot)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