On Mon, May 1, 2017 at 7:45 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 05/01/2017 04:36 PM, Max Wang wrote: > >> Hi Amitabh, >> >> I mean the serial sequence that controls the id value has been set to 1 >> for all tables. That's why I got the duplicate key value error when I tried >> to insert the new record to table. >> > > So what does the sequence query show? > > As an example in psql: > > test=# create table serial_test(id serial PRIMARY KEY, fld_1 varchar); > CREATE TABLE > > test=# \d serial_test > Table "public.serial_test" > Column | Type | Modifiers > --------+-------------------+------------------------------- > --------------------------- > id | integer | not null default > nextval('serial_test_id_seq'::regclass) > fld_1 | character varying | > Indexes: > "serial_test_pkey" PRIMARY KEY, btree (id) > > The above shows that the sequence associated with the serial type is: > 'serial_test_id_seq' > > The below shows how to select from that sequence: > > test=# select * from serial_test_id_seq ; > -[ RECORD 1 ]-+-------------------- > sequence_name | serial_test_id_seq > last_value | 1 > start_value | 1 > increment_by | 1 > max_value | 9223372036854775807 > min_value | 1 > cache_value | 1 > log_cnt | 0 > is_cycled | f > is_called | f > > > Can you do that on the serial column from one the affected tables and post > the results here? > > > > >> Thanks. >> >> Regards, >> Max >> >> -----Original Message----- >> From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] >> Sent: Tuesday, 2 May 2017 9:31 AM >> To: Max Wang <mw...@1080agile.com>; Amitabh Kant <amitabhk...@gmail.com> >> Cc: pgsql-general@postgresql.org >> Subject: Re: [GENERAL] all serial type was changed to 1 >> >> On 05/01/2017 04:11 PM, Max Wang wrote: >> >>> Hi Amitabh, >>> >>> >>> >>> Thank you for suggestion. We did not reach the limit of serial type. >>> Some tables only have hundreds of rows. >>> >> >> It would helpful if you ran the query I showed in my previous post on one >> the sequences just so we can see. >> >> From subsequent post of yours: >> >> "Sorry. I mean all tables’ id column were reset to 1." >> >> I thought I understood on this, now I am not sure. Do you mean that the >> actual values in the id column in all the tables have been set to 1 or that >> the serial sequence that controls the id value has been set to 1? >> >> >>> >>> >>> Regards, >>> >>> Max >>> >>> >>> >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > *Hmmm, it's beginning to look like someone did a "SELECT setval('seqname', 1);" For every sequence. * *Is that a possibility?* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.