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
