Received: from albatross.prod.itd.earthlink.net (albatross.mail.pas.earthlink.net [207.217.120.120]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1EJuDE65524 for ; Thu, 14 Feb 2002 14:56:14 -0500 (EST) (envelope-from ron@sattargroup.com) Received: from user-119ah3k.biz.mindspring.com ([66.149.68.116] helo=[10.0.1.5]) by albatross.prod.itd.earthlink.net with esmtp (Exim 3.33 #1) id 16bRzD-0006Hn-00 for pgsql-sql@postgresql.org; Thu, 14 Feb 2002 11:56:08 -0800 Mime-Version: 1.0 X-Sender: ron@mail.sattargroup.com Message-Id: Date: Thu, 14 Feb 2002 13:55:28 -0600 To: pgsql-sql@postgresql.org From: ron Subject: nexval error duplicate key Content-Type: multipart/alternative; boundary="============_-1198405928==_ma============" X-Archive-Number: 200202/160 X-Sequence-Number: 6586 --============_-1198405928==_ma============ Content-Type: text/plain; charset="us-ascii" ; format="flowed" 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 --============_-1198405928==_ma============ Content-Type: text/html; charset="us-ascii" nexval error duplicate key
       

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
--============_-1198405928==_ma============--