Re: Default ordering option

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(dot)klaver(at)aklaver(dot)com