Re: nexval error duplicate key - Mailing list pgsql-sql

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