Re: nexval error duplicate key

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