CODEWITHSUNDEEP
postgresqlsql-update
Dman Cannon
Dman Cannon

Reputation: 126

Records in the table re-arrange sometimes when doing update operation in Postgres database

I have a table which contains details of patients. The table contains 5 columns:

row 1: food - spicy food - avoid spicy food - <patient id> - <primary key id>
row 2: drugs - parametacol - avoid for 1 day - <patient id> - <primary key id>

Note: hypen separates the column.

When doing update on some row, Assume Row 1 changing the 3rd col, when doing a simple update query rearranges the rows.

Update <tableName> set <3rd Col name> = 'Avoid oily food' where <primary key id col name> = '<id>'

This simple query often rearranges the rows, like this.

    row 2: drugs - parametacol - avoid for 1 day - <patient id> - <primary key id>
    row 1: food - spicy food - Avoid oily food - <patient id> - <primary key id>

I dont want it to re-arrange. I want them to be in the same order as before updating. Thanks in Advance.

Upvotes: 0

Views: 156

Answers (1)

sticky bit
sticky bit

Reputation: 37487

Tables in a relational database represent relations. Relations are a special form of (multi) sets and sets have no inherent order. So does a table. So your premise, that the order changes is wrong all together.

Of course the data has to be stored and returned, when queried, in some order physically. But the DBMS is free to chose any order it "likes", any time -- even the exact same query can produce different orders each time it is executed.

The only way to get a defined order is to explicitly use an ORDER BY clause when querying. Find or create a column or set of columns that define the order you want and use it/them in an ORDER BY clause every time you query the table.

Upvotes: 1

Related Questions