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.klaver@aklaver.com