sql - How keep data don't sort?

Question

I have a Table A:

Col1 Col2
12    a
12    c
12    b

If i code : Select * from A; Result is:

Col1 Col2
12    a
12    b
12    d

I want get data is:

Col1 Col2
12    a
12    c
12    b

How get data don't sort?

Answer 1

The crucial thing to understand is that SQL tables do not have an ordering. The order of rows you see when you SELECT without an ORDER BY only stays the same because it's quicker for the database to get them in that order than some other order. PostgreSQL will only return rows in this order when you do a sequential scan on the table; if it can use an index for the query then you will generally get the rows in some other order.

You might find this answer I wrote earlier informative.

In PostgreSQL, UPDATEs to rows can move them to a different location within the table, changing the order they're returned in. So can the background autovacuum process and various other operations like VACUUM and CLUSTER.

So you must never rely on the "default" ordering for anything. If you want to give rows some kind of order, they must have a key on which you can sort them.

If you have created a table without a key and now realise it should have one, you might be able to recover from the situation by using the ctid system column. Do not rely on this for production use, it's a system-internal column that's visible to users only for emergency recovery and diagnostics purposes. First, see if the physical on-disk ordering is actually the order you want:

SELECT row_number() OVER () AS mytable_id, *
FROM mytable
ORDER BY ctid;

If it is, you can add a new key column that's pre-set to an auto-incremented key applied in the on-disk row order. There are two ways to do this. The safest is:

BEGIN;
LOCK TABLE mytable IN ACCESS EXCLUSIVE MODE;
ALTER TABLE mytable RENAME TO mytable_old;

CREATE TABLE mytable (id SERIAL PRIMARY KEY, LIKE mytable_old INCLUDING ALL);

INSERT INTO mytable
SELECT row_number() OVER () AS id, *
FROM mytable_old
ORDER BY ctid;

SELECT setval('mytable_id_seq', (SELECT max(id)+1 FROM mytable));

COMMIT;

then once you're sure you're happy with the results, DROP TABLE mytable_old;. See this demo: http://sqlfiddle.com/#!12/2cb99/2

A quick and easy but less safe way is to just create the column and rely on PostgreSQL re-writing the table from beginning to end:

ALTER TABLE mytable ADD COLUMN mytable_id SERIAL PRIMARY KEY;

There is absolutely no guarantee that PostgreSQL will assign the IDs in order, though in practice it will do so. See this SQLFiddle demo.

Be aware that when you use a SEQUENCE (which is what a SERIAL column creates) there are a few behaviours you might not expect. When you're inserting multiple rows at once, the rows might not necessarily get assigned IDs in the exact order you expect them to, and they might "appear" (become visible) in a different order to the order they were assigned IDs and inserted in. Also, if transactions roll back, the generated ID is thrown away forever, so you get gaps in the numbering. This is very good if you want your database to be fast, but it's not ideal if you want gap-less numbering. If that's what you need, search for "postgresql gapless sequence".