Search Postgresql Archives

Re: all serial type was changed to 1

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux