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

On 06/12/2018 01:39 AM, Steve Krenzel wrote:
> 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.

That will only work until some other INSERT or UPDATE occurs. Using
table from your example:

UPDATE bar SET val = 'C1' where id = 1;

TABLE bar;
id | val
----+-----
2 | B
3 | A
4 | C
5 | B
6 | A
1 | C1
(6 rows)

You can use CLUSTER:

https://www.postgresql.org/docs/10/static/sql-cluster.html

to reestablish order based on an index, though that has the same issue:

"Clustering is a one-time operation: when the table is subsequently
updated, the changes are not clustered."

As has been pointed out order of rows is not guaranteed.

>
> On Tue, Jun 12, 2018 at 1:24 AM Ravi Krishna <sravikrishna3(at)gmail(dot)com
> <mailto: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.
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com