Received: from jupiter.hub.org (jupiter.hub.org [64.49.215.5]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1F1sCE25252 for ; Thu, 14 Feb 2002 20:54:12 -0500 (EST) (envelope-from news@jupiter.hub.org) Received: (from news@localhost) by jupiter.hub.org (8.11.6/8.11.6) id g1F1RZG23157 for pgsql-sql@postgresql.org; Thu, 14 Feb 2002 19:27:35 -0600 (CST) (envelope-from news) From: missive@frontiernet.net (Lee Harr) X-Newsgroups: comp.databases.postgresql.sql Subject: Re: nexval error duplicate key Date: Fri, 15 Feb 2002 01:27:35 +0000 (UTC) Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 132 Message-ID: References: Reply-To: missive@hotmail.com X-Complaints-To: news@hub.org User-Agent: slrn/0.9.7.0 (FreeBSD) To: pgsql-sql@postgresql.org X-Archive-Number: 200202/267 X-Sequence-Number: 6690 > 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!)