pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: missive@frontiernet.net
To: pgsql-sql@postgresql.org
Subject: Re: nexval error duplicate key
Date: Fri, 15 Feb 2002 01:27:35 +0000 (UTC)
Message-ID: <a4ho65$mj8$1@jupiter.hub.org> (raw)
References: <a05100309b891c1fa5fd5@[10.0.1.5]>

> CREATE TABLE name_table (
>          id   SERIAL,
>          name_insert TEXT
>      );
> 
> 
> Basically when the name is inserted it works fine
> 
> I check the table I get the following
> 
> for example DATA 1
> 	1 john
> 	2 mary
> 	3 josephine
> 	4 ron
> 
> Then here is the issue
> 
> 	I modify 2 mary to 2 jane
> 	the database modified to
> 

How are you doing this? From ColdFusion? How exactly?
If, for instance, CF is using setval() for instance... trouble!

> 	DATA 2
> 
> 	1 john
> 	3 josephine
> 	4 ron
> 	2 jane
> 
> 
> When I use postgresql admin I get DATA 2
> 
> the number is OUT of order
> 

If you are using UPDATE to make the change, ie:
UPDATE name_table SET name_insert='jane' WHERE id=2;
then the order does not matter. The order of the rows
stored in the database is not guaranteed. If you want
them in a certain order when you SELECT, you need
to ORDER BY id (for instance)


> NOW when I add a new value
> 
> it give me an error that states:
> 
> --------------------------------------------------------
> 
> Error Occurred While Processing Request
> 
> Error Diagnostic Information
> 
> ODBC Error Code = 08S01 (Communication link failure)
> 
> Error while executing the query; ERROR: Cannot insert a duplicate key 
> into unique index name_pkey
> 
> The error occurred while processing an element with a general 
> identifier of (CFQUERY), occupying document position (1:61) to 
> (1:110).
> --------------------------------------------------------
> 

Again, how are you doing this? It might help to see the
relevant SQL code.


> I only put the relavent error above
> 
> It seems when the nextval is used here the sql thinks the last value 
> is '3' because the last value is '2 jane' so it seems logical to get 
> '3' as the next value.
> 
> Additional information:
> 
> If I move the highest value '4 ron'  by editing it, just as I edited 
> '2 jane' then I get '4 ronald'
> 
> 	DATA 3
> 
> 	1 john
> 	3 josephine
> 	2 jane
> 	4 ronald
> 

So you UPDATE ...SET ... WHERE id=4

> 
> Now when I add a new record like 'sam' no error
> 
> 
> 	DATA 3
> 
> 	1 john
> 	3 josephine
> 	2 jane
> 	4 ronald
> 	5 sam
> 
> so now it works
> 
> What's the deal?
> 
> doesn't 'nexval' find the greatest value? or does it seem to get the 
> last value in the list
> 

Well. For one thing, the PostgreSQL function is called
nextval()
so I am curious about what you are doing exactly.

> so I am thinking the best way is to sort the table OR I am thinking 
> that when I find the next value it does a sort then find the last 
> value
> 

Order of rows in the database has no effect.
As long as you are only inserting using something like

INSERT INTO name_table (name_insert) VALUES ('bob');

you will not have any trouble.
Let's see your code.

PS. please do not post HTML to the list. (Thank you!)





view thread (2+ messages)

Message-ID: <a4ho65$mj8$1@jupiter.hub.org>
Permalink:  ../a4ho65$mj8$1@jupiter.hub.org/
Also on:    postgresql.org/message-id/a4ho65$mj8$1@jupiter.hub.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: missive@frontiernet.net
  Subject: Re: nexval error duplicate key
  In-Reply-To: <a4ho65$mj8$1@jupiter.hub.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox