Re: Semantics around INSERT INTO with SELECT and ORDER BY.

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.

On Tue, Jun 12, 2018 at 1:24 AM Ravi Krishna <sravikrishna3(at)gmail(dot)com>
wrote:

> 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.
>