PostgreSQL Rearranges My Column Data

CarlosinFL · #1 (**permalink**) 04-06-11, 21:52

Is there a reason that PostgreSQL re-arranges my column data after I make a change?

For example:

Code:

iamunix=# SELECT * FROM zoo;
 id | animal | color | name  |    dob     |    dod
----+--------+-------+-------+------------+------------
  1 | snake  | black | Marty | 2009-05-19 |
  2 | bird   | blue  | Jake  | 2011-09-03 |
  3 | monkey | brown | Simon | 1981-02-11 | 2011-02-13
(3 rows)

Now if I edit id 1 or 2 with ALTER, the next time I run the same command above, I now have:

Code:

iamunix=# SELECT * FROM zoo;
 id | animal | color | name  |    dob     |    dod
----+--------+-------+-------+------------+------------
  2 | bird   | blue  | Jake  | 2011-09-03 |
  3 | monkey | brown | Simon | 1981-02-11 | 2011-02-13
  1 | snake  | black | Marty | 2009-05-19 |
(3 rows)

Is there a logical reason for this? I've only noticed this in PostgreSQL.

r937 · #2 (**permalink**) 04-06-11, 22:40

Quote:

Originally Posted by CarlosinFL

Is there a logical reason for this?

yes

the reason is your ORDER BY clause

shammat · #3 (**permalink**) 04-07-11, 02:32

Quote:

Originally Posted by CarlosinFL

I've only noticed this in PostgreSQL.

Actually all DBMS behave like that.

As r937 has pointed out, the lack of an ORDER BY is the reason.

The only way to guarantee a specific order of your row is to use an ORDER BY

Actually you don't necessarily need to change the data to see this behaviour
PostgreSQL (or other DBMS). PostgreSQL can e.g. use the result of another's session table scan for your statement (to optimize I/O).

Unless you explicitely request a sort order the DBMS is free to return the rows in any order it seems suitable (to speed up the processing)

Btw: your terminology is a bit confusing. You did not "edit" the rows using "ALTER" but "UPDATE"

CarlosinFL · #4 (**permalink**) 04-07-11, 11:49

Quote:

Originally Posted by shammat

Actually all DBMS behave like that.

I guess coming from MySQL and relating their behavior to PostgreSQL isn't good considering they're very non compliant. I since have switched to PostgreSQL and assumed it would do the same w/o having to use the ORDER BY command.

Thanks for the heads up.

Internet Services

Web Development

Digital Marketing

Consumer Tech