Re: re: duplicate key

Patrick Coulombe wrote:
>medias=> insert into medias (name, location) values ('BLABLA',
>'Montreal');
>ERROR: Cannot insert a duplicate key into a unique index
>
>
>Here's my database & tables :
>
>Database = medias
>+------------------+----------------------------------+----------+
>| Owner | Relation | Type |
>+------------------+----------------------------------+----------+
>| postgres | medias | table |
>| postgres | medias_media_id_key | index |
>| postgres | medias_media_id_seq | sequence |
>+------------------+----------------------------------+----------+
>
>Table = medias
>+-------------------------+----------------------------------+-------+
>| Field | Type | Length|
>+-------------------------+----------------------------------+-------+
>| media_id | int4 not null default nextval('" | 4 |
>| name | text not null | var |
>| location | text | var |
>+-------------------------+----------------------------------+-------+
>
>
>I don't specify a value to media_id, so why i got this error? It's
>supposed to increment by itself (media_id serial). But, i have to say
>that i import the data in medias with the function copy from... i import
>also media_id information. Maybe now, i cannot add because my
>medias_media_id_seq do not correspond to my new "importation". How can I
>fix that?

and also wrote:
>last post to pgsql-sql, i will post to novice ;)
>how can i change the "last_value" from a sequence?
>
>when i try this :
>medias=> update medias_media_id_seq set last_value = 2329;
>ERROR: You can't change sequence relation medias_media_id_seq

You need to initialise the sequence value:

SELECT setval(medias_media_id_seq, 2329);

and do this before you insert new data, otherwise the insert will use a value
that may conflict with values already in the table.

You will need to do this if the table has been created by copy or if records
have been inserted with the sequence field value explicitly stated (since in
that case the sequence value has not been updated).

To see the current value (in your session) of the sequence:

SELECT currval(medias_media_id_seq);

To increment the sequence (without creating a row in the table):

SELECT nextval(medias_media_id_seq);

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP key from public servers; key ID 32B8FAA1
========================================
"We are troubled on every side, yet not distressed; we
are perplexed, but not in despair; persecuted, but not
forsaken; cast down, but not destroyed; Always bearing
about in the body the dying of the Lord Jesus, that
the life also of Jesus might be made manifest in our
body." II Corinthians 4:8-10