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@gmail.com 
> <mailto:sravikrishna3@gmail.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.klaver@aklaver.com