pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: ron <ron@sattargroup.com>
To: pgsql-sql@postgresql.org
Subject: nexval error duplicate key
Date: Thu, 14 Feb 2002 13:55:28 -0600
Message-ID: <a05100309b891c1fa5fd5@[10.0.1.5]> (raw)



I am getting the darnest error here

I think I narrowed it down

Problem:

In a simplified version of the program is the following, this is 
completed in ColdFusion which I don't think is an issue

My understanding of SQL database is limited

I set up a table

field
	id
	name_insert


I basically want to add a name in the 'name_insert' field and let 
'id' to incrementally increase automatically

An html  was created with ONLY one field to add a name
An html was created to list the names and modify the name


I used the code something like the following:

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

	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

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).
--------------------------------------------------------

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


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

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

so I am stuck any ideas of what I can do?

Ron Sattar

-- 


		Sattar Group, Inc.	ICQ 153 46 249
		Voice 773 725 3445	Fax 773 442 0064
			http://www.SattarGroup.com

view thread (2+ messages)  latest in thread

Message-ID: <a05100309b891c1fa5fd5@[10.0.1.5]>
Permalink:  ../a05100309b891c1fa5fd5@%5B10.0.1.5%5D/
Also on:    postgresql.org/message-id/a05100309b891c1fa5fd5@[10.0.1.5]

 · 

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: ron@sattargroup.com
  Subject: Re: nexval error duplicate key
  In-Reply-To: <a05100309b891c1fa5fd5@[10.0.1.5]>

* 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