Re: AutoIncrement not working

The sequence needs to be reset:

select setval('datadef_datadefindex_seq',(select max(datadefindex) from
datadef));

This will do it for you.

> -----Original Message-----
> From: pgsql-admin-owner(at)postgresql(dot)org
> [mailto:pgsql-admin-owner(at)postgresql(dot)org]On Behalf Of Ben Kassel
> Sent: Monday, November 04, 2002 12:22 PM
> To: pgsql-admin(at)postgresql(dot)org
> Subject: [ADMIN] AutoIncrement not working
>
>
> When I try to create a new row in this table and do not explicitly
> define a unique value for datadefindex I get the following error
> message:
>
> ERROR: Cannot insert a duplicate key into unique index datadef_pkey
>
> Here is the INSERT statement that generated the error:
>
> tmdb=# insert into datadef (cfgmgmtid, datadefname, datadefformat,
> datadefunits, datadefdescription)
> VALUES (2, 'TestValue', 'REAL', 'N/A', 'This is a test of placing a new
>
> row without an explicit datadefindex');
>
> Here is the table definition:
>
> tmdb=# \d datadef
> Table "datadef"
> Column | Type |
> Modifiers
>
>
> --------------------+-----------------------+---------------------------
>
> --------
> -------------------------
> datadefindex | integer | not null default
> nextval('datadef_
> datadefindex_seq'::text)
> cfgmgmtid | integer |
> datadefname | character varying(80) | not null
> datadefformat | character varying(80) | not null
> datadefunits | character varying(80) | not null
> datadefdescription | text | not null
> Primary key: datadef_pkey
> Unique keys: datadefname_idx
> Triggers: RI_ConstraintTrigger_19507,
> RI_ConstraintTrigger_19509,
> RI_ConstraintTrigger_19511,
> RI_ConstraintTrigger_19513,
> RI_ConstraintTrigger_19515,
> RI_ConstraintTrigger_19659,
> RI_ConstraintTrigger_19661,
> RI_ConstraintTrigger_19663,
> RI_ConstraintTrigger_19665,
> RI_ConstraintTrigger_19667
>
> And finally here is the entry in the datadef_datadefindex_seq table:
>
> tmdb=# select * from datadef_datadefindex_seq;
> sequence_name | last_value | increment_by | max_value |
> min_value |
> cache_value | log_cnt | is_cycled | is_called
>
> --------------------------+------------+--------------+------------+----
>
> -------+
> -------------+---------+-----------+-----------
> datadef_datadefindex_seq | 8 | 1 | 2147483647 |
> 1 |
> 1 | 32 | f | t
> (1 row)
>
> Notice that last_value = 8, owever the current number of rows in the
> datadef table = 67.
>
> My current workaround is to do a MAX(datadefindex) on datadef,
> increment it by one and explicitly place that value as the
> datradefindex for the new row, however I am worried about the database
> stability.
>
> More information : If I DROP the database, recreate it, and enter
> values into the table manually, the autoincrement works on this table.
> It seems that the problem arises after I reload the data into the table
> using the \i command on a file which was created using the pg_dump
> command.
>
> I have recently upgraded from 7.2.1 to 7.2.3 using the RPM.
>
> Thanks in advance,
>
> ben
>
> ---------------------------(end of broadcast)---------------------------
>
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>