Re: nexval error duplicate key
От | missive@frontiernet.net (Lee Harr) |
---|---|
Тема | Re: nexval error duplicate key |
Дата | |
Msg-id | a4ho65$mj8$1@jupiter.hub.org обсуждение исходный текст |
Ответ на | nexval error duplicate key (ron <ron@sattargroup.com>) |
Список | 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!)
В списке pgsql-sql по дате отправления: