Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    110

    Unanswered: Updating existing data in a table

    I noticed that when i update a certain row in postgresql the last updated row will be at the end of a select * from table name.

    Example;

    test=# select * from tafel;
    col1 | col2 | col3
    ------+------+------
    8 | 8 | 5
    9 | 898 | 985
    10 | 10 | 986
    (3 rows)

    Ok, now i update a row,

    test=# update tafel SET col2=3,col3=3 where col1=8;
    UPDATE 1

    Update complete now lets check out a select.

    test=# select * from tafel;
    col1 | col2 | col3
    ------+------+------
    9 | 898 | 985
    10 | 10 | 986
    8 | 3 | 3

    Hmm strange.. my primary key (col1) no longer has an ascending order. Sure i can do an order by, but doesnt this affect the index on the primary key? Or is the pointer still located before the 9 in the col1 column?

    I can imagine this giving a huge mess when you have 200000 or so rows.. and every now and then you update a row and you are therefor forced doing a complete table scan.

    Cant remember seeing an explination for this in the documentation (and wasnt able to find an anwser using google) but on the otherhand, im dutch so i could have just missed it.

    -Ed

  2. #2
    Join Date
    Mar 2004
    Posts
    110
    I talked to a dba about it in our company. He is not that familiar with postgresql but wasnt sure if a primary key is considered as an index.

    I reckon, after seeing the results, a primary isnt an index, which would be kinda strange imho.

Posting Permissions

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