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@xxxxxxxxx
<mailto:sravikrishna3@xxxxxxxxx>> 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@xxxxxxxxxxx
