On 7/24/19 1:45 AM, Cyril Champier wrote:
Thanks for your answers.
Unfortunately the update trick only seems to work under certain conditions.
I do this to shuffle my patients table:
UPDATE "patients"
SET "updated_at" = NOW()
WHERE "patients"."id" = (SELECT "patients"."id" FROM "patients" ORDER BY
random() LIMIT 1)
Then indeed, this query returns different order:
SELECT *
FROM "patients"
But this one (because it use an index?) always returns values in the
same order:
SELECT "id"
FROM "patients"
Hmm, I don't see that:
test=# \d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
a | integer | | not null |
b | character varying | | |
Indexes:
"t1_pkey" PRIMARY KEY, btree (a)
test=# select * from t1;
a | b
---+---------
2 | cat
3 | fish
1 | dogfish
(3 rows)
test=# select a from t1;
a
---
2
3
1
(3 rows)
Are you sure there is nothing going on between the first and second
queries e.g. ROLLBACK?
And for the other suggestion, I cannot blindly add 'ORDER BY random()'
to every select,
because of the incompatibility with distinct and union, and the way we
use our orm.
Are you talking about the production or test queries above?
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
