CODEWITHSUNDEEP
sqlpostgresql
huber.duber
huber.duber

Reputation: 796

Postgresql: row number changes on update

I am new to Postgresql and I am using version 9.3. I have a table in which i have a couple of rows. My question is, when I update a row, the row number is changed and it is moved to the the last location in the table. My question is: is this the default behavior, because I think when a row is updated, it should not be moved from its place? The operation seems to be like deleting and then inserting the row again.

Here is the sample SQL:

CREATE TABLE cities
(
    city_id serial, 
    city_name character varying(50), 
    PRIMARY KEY (city_id)
);

INSERT INTO cities (city_name) VALUES ('ABC');
INSERT INTO cities (city_name) VALUES ('DEF');
INSERT INTO cities (city_name) VALUES ('GHI');
INSERT INTO cities (city_name) VALUES ('JKL');

UPDATE cities
    SET city_name = 'XYZ'
    WHERE city_id = 1;

Now, on:

SELECT * FROM cities;

moves the updated row with id 1 at the last location.

2, DEF
3, GHI
4, JKL
1, XYZ

Thanks

Upvotes: 5

Views: 1782

Answers (2)

svaor
svaor

Reputation: 2245

Yes, table is an unordered set by definition.

But PostgreSQL has an architecture feature shown below:

CREATE TABLE cities
(
    city_id serial, 
    city_name character varying(50), 
    PRIMARY KEY (city_id)
);

INSERT INTO cities (city_name) VALUES ('ABC');
INSERT INTO cities (city_name) VALUES ('DEF');
INSERT INTO cities (city_name) VALUES ('GHI');
INSERT INTO cities (city_name) VALUES ('JKL');

SELECT ctid, city_id, city_name FROM cities;
-- (0,1)    1   ABC
-- (0,2)    2   DEF
-- (0,3)    3   GHI
-- (0,4)    4   JKL

UPDATE cities
    SET city_name = 'XYZ'
    WHERE city_id = 1;

SELECT ctid, * FROM cities;
-- (0,2)    2   DEF
-- (0,3)    3   GHI
-- (0,4)    4   JKL
-- (0,5)    1   XYZ

So simplistically speaking an UPDATE in PostgreSQL is not much different from a DELETE followed by an INSERT or is Copy-On-Write procedure.

That is why SELECT result after UPDATE is different by default. And that is why PostgreSQL RDBMS has such features like tuple version, dead tuples, VACUUM (kind of garbage collector), Heap Only Tuples (HOT) and so on. That is why a table with single row or even without any rows in PostgreSQL can have physical size much more than expected after massive DML.

Just a feature.

Upvotes: 0

user330315
user330315

Reputation:

the row number is changed

There is no such thing as a "row number" in a relational table.

I think when a row is updated, it should not be moved from its place

As a row doesn't have a "place", there is no such thing as "moving" it out of that either.

Rows in a relational table are not sorted.

Think of them like balls in a basket.

Without an ORDER BY the DBMS is free to return the rows in any order it thinks is OK.

If you need to have the rows ordered in a certain way the only (really: the only) way to get that is to use an ORDER BY statement.

Upvotes: 10

Related Questions