Skip site navigation (1) Skip section navigation (2)

Re: re: duplicate key

From: "Oliver Elphick" <olly(at)lfix(dot)co(dot)uk>
To: Patrick Coulombe <pcoulombe(at)mediacces(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: re: duplicate key
Date: 2000-05-22 20:57:35
Message-ID: 200005222057.e4MKvZJ26994@linda.lfix.co.uk (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-sql
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 



In response to

pgsql-sql by date

Next:From: Erol OzDate: 2000-05-23 08:10:25
Subject: Re: OID
Previous:From: Patrick CoulombeDate: 2000-05-22 06:53:42
Subject: re: duplicate key

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group