Thread: List sequence assigned to primary key

I added a few records to a table and assigned numbers to the id field - now when I
try to add records properly, it says that it can't accept duplicate values on the
primary field (id)

Although there were only a few records and I could just redo them, I wonder if
there is another method (for future reference)

So here are my two questions

1. How can you list (find out) the sequence name assigned to a primary key?

2. How can you change that sequence value?


I searched a bunch of web sites but had trouble narrowing my search to less than
about 30,000 hits

Brian Johnson wrote:
> I added a few records to a table and assigned numbers to the id field
> - now when I try to add records properly, it says that it can't
> accept duplicate values on the primary field (id)
>
> Although there were only a few records and I could just redo them, I
> wonder if there is another method (for future reference)
>
> So here are my two questions
>
> 1. How can you list (find out) the sequence name assigned to a
> primary key?
>
The sequence name for a serial field is:
tablename_fieldname_seq
eg:
CREATE TABLE tst (x SERIAL);
would create a sequence called:
tst_x_seq

> 2. How can you change that sequence value?
>
SELECT setval('seq_name',seq_value)
eg:
SELECT setval('tablename_fieldname_seq',MAX(field_name) FROM tablename;
To resync a sequence with a field.
hth,
- Stuart