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