postgres thing that bugs me

arnihr

I've got a field called ID, with the type of serial. I need to make it possible for me to update the rows in my table without the ID messing up...

e.g. I got row with the ID 3. I want to update that row. I've got total 99 rows in my table. I update the row and now the rows ID is 100. It still inrements when it shouldn't.

Someone told me to use triggers/functions but I have no idea how to do that... so any suggestions are well taken.

With thanks in advance,
Orny

kparker

You must not be updating the row; it sounds more like you're deleting the old copy and inserting a new copy.

arnihr

O but I am updating 🙂
I know what I'm doing trust me, it just happens... have you tried postgres? If not, don't say this doesn't happen, I'm not the only one. I did this exact command:
UPDATE stats SET ip = '10.169.251.11' WHERE id = '423'

The ID shows itself as 423 but actually is 425 in the table 🙁

Any thougts?

Anon

You are not updating the row. Can you post your SQL statement? It should look something like this,

UPDATE table_name SET content='This is how we do it, baby.' WHERE id='3';

This should do the job.

No Worries.

Brett

Anon

What version of PostgreSQL are you using. I update data (w/ serial id) with PG 6.5-7.02 all of the time. I have never seen this problem.

Brett

toma42

Well, I've use postgres and never seen this. I would first suggest you change your column name to something meaningful. An id could be a user id, a primary key, a ANYTHING. Personally, I name my primary keys after their table and append _key such as table_key and I reference them using ref_table.

FYI, a serial column is a triggered column...the trigger is created when the row is inserted.

Can you reproduce this by using psql from shell?

toma42

FYI, a serial column is a triggered column...the trigger is created when the row is inserted

Whatever Tom. The trigger is created when the column is created. Oops

[deleted]

"I know what I'm doing trust me..."

If I had a penny for every person who said that and later found out they didn't do what they think they were doing... 🙂

So you are saying that in your database you can update a record with
ID=2, then go back to look at the record and the ID of that record has changed to a new value even if you did not change it manually?

arnihr

I'm saying that the ID itself doesn't change! I'm saying that the order changes...

This is the order after update:
422 194.144.122.228 2001-07-10 14:46:42+00 www.fjolnet.net Edit Delete
424 10.169.1.30 2001-07-10 14:49:11+00 www.fjolnet.net Edit Delete
423 10.169.251.11 2001-07-10 14:48:15+00 www.fjolnet.net Edit Delete

And this affects when I do ORDER BY and selecting whatever I need to select.

[deleted]

Aha, so it's just the 'unordered' order that changes.

Well that normal, because a database has complete freedom about how it stores it's records, and if it decides that record 424 fits better in the space just before 423, then so be it.

That why you use ORDER BY. The order of the values in the ORDER BY column should never change id the ID's don't change.

arnihr

I shall say no more.
I forgot that damn ORDER BY 🙂

Thanks...
I wanna crawl under a couch and die.

[deleted]

Caffeine is the answer... all hail the mighty caffeine! :-) lol

kparker

Not to mention, it's probably pretty crowded under that couch, anyway...