Is PostgreSQL order fully guaranteed if sorting on a non-unique attribute? [duplicate]

Question

Possible Duplicate:
Why do results from a SQL query not come back in the order I expect?

From reading 7.5 Sorting Rows and from issues I've seen with PostgreSQL, my impression is the following, but that section is not fully explicit, so I would be grateful if someone could verify:

SELECT * FROM items;

has no guaranteed order.

SELECT * FROM items ORDER BY published_date ASC;

guarantees that two items with different dates come in a given order, but does not guarantee that two items with the same date always come in the same order.

SELECT * FROM items ORDER BY published_date ASC, id ASC;

always returns items in the same order, since it is fully deterministic.

Do I have this right?

I'm not quite clear about whether sorting on one attribute (such as published_date) guarantees the order for records with the same value, as in the second example.

Answer 1

Order is not guaranteed unless you explicitly specify it with the ORDER BY clause.

You might be getting data in the same order upon several executions in case there is no database activity, as PostgreSQL will just return rows in the order it finds them in the database pages. Do a small test:

• insert a number of rows keeping the desired order;
• query the table: you will get rows ordered;
• update the very first record in the set;
• query the table again;
• observe the results.

In short: You might be even getting rows in the desired order, but this is just a coincidence.