Re: re: duplicate key - Mailing list pgsql-sql

Patrick Coulombe wrote: >medias=> insert into medias (name, location) values ('BLABLA', >'Montreal'); >ERROR:  Cannot
inserta 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
igot this error? It's >supposed to increment by itself (media_id serial). But, i have to say >that i import the data in
mediaswith the function copy from... i import >also media_id information. Maybe now, i cannot add because my
>medias_media_id_seqdo 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? >
>wheni try this : >medias=> update medias_media_id_seq set last_value = 2329; >ERROR:  You can't change sequence
relationmedias_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.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver              PGP key from public servers; key
ID32B8FAA1                ========================================    "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;
Alwaysbearing     about in the body the dying of the Lord Jesus, that      the life also of Jesus might be made
manifestin our      body."        II Corinthians 4:8-10