Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Unanswered: PostgreSQL Rearranges My Column Data

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by CarlosinFL View Post
    Is there a logical reason for this?
    yes

    the reason is your ORDER BY clause

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by CarlosinFL View Post
    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"

  4. #4
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Quote Originally Posted by shammat View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •