From: | Myles Miller <pg(at)q7r7(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | random generated string matching index in inexplicable ways |
Date: | 2019-05-07 11:53:45 |
Message-ID: | 20190507115345.GA56585@50pop.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
PROBLEM:
Strings or characters generated by any random function (including pg_crypto
gen_random_bytes) are matching a string/char index in surprising ways.
Reduced down to its simplest example:
-- use random 0 or 1, plus 65, to get 'A' or 'B'
# SELECT chr(round(random())::int + 65);
chr
-----
B
(1 row)
# SELECT chr(round(random())::int + 65);
chr
-----
A
(1 row)
-- simple table for matching:
CREATE TABLE x( y char(1) primary key );
INSERT INTO x(y) VALUES ('A');
INSERT INTO x(y) VALUES ('B');
-- if I query 'A' or 'B' it works as expected
# SELECT y FROM x WHERE y = 'A';
y
---
A
(1 row)
# SELECT y FROM x WHERE y = 'B';
y
---
B
(1 row)
-- if we use random-generated 'A' or 'B', things get inexplicable
# SELECT y FROM x WHERE y = chr(round(random())::int + 65);
y
---
A
B
(2 rows)
# SELECT y FROM x WHERE y = chr(round(random())::int + 65);
y
---
(0 rows)
# SELECT y FROM x WHERE y = chr(round(random())::int + 65);
y
---
B
(1 row)
I've been wrestling with this for hours. Any suggestions? Thank you.
From | Date | Subject | |
---|---|---|---|
Next Message | Lu, Dan | 2019-05-07 12:12:18 | Question on binding VIP to Postgresql instance |
Previous Message | Nicklas Avén | 2019-05-07 07:49:11 | Re: PostgreSQL on Amazon RDS |