This is relevant for tables that have a column with a SERIAL type, I need to guarantee that the relative ordering remains the same as the ordering of the selected result set.
More concretely, given:
> CREATE TABLE foo (id SERIAL, val TEXT);
> CREATE TABLE bar (id SERIAL, val TEXT);
> INSERT INTO foo (val) VALUES ('A'), ('B'), ('C');
> TABLE foo;
id | val
----+-----
1 | A
2 | B
3 | C
(3 rows)
Then,
> INSERT INTO bar (val) SELECT val FROM foo ORDER BY id DESC;
> TABLE bar;
id | val
----+-----
1 | C
2 | B
3 | A
(3 rows)
The rows should be inserted in reverse. (Note: I don't care about the actual value of the id, only the relative ordering).
Inserting more values should similarly append into the table in order (where "append" is used in terms of the serial id).
> INSERT INTO bar (val) SELECT val FROM foo ORDER BY id DESC;
> TABLE bar;
id | val
----+-----
1 | C
2 | B
3 | A
4 | C
5 | B
6 | A
(6 rows)
Or to put it another way, I want to select values from one table ordered by complex criteria and insert them into another table. I want to be able to retrieve the rows from the target table in the same order they were inserted, but I don't care about the specific ordering criteria. I only care about the order they were inserted.
Why is it even important? Once you use ORDER BY clause, you are guaranteed to get the rows in the order. Why do you need how it was inserted in the first place.