Hi all,
I'm trying to generate a series of random strings (capital A-Z)
between 2 and 5 characters long (say, 10^6).
I'm using a recursive CTE to achieve this.
A fiddle is available here:
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=206a0c522e853f043c7e633d19852de2
The SQL:
WITH RECURSIVE rand (num, md, a_2_s) AS
(
SELECT
1,
MD5(RANDOM()::TEXT),
ARRAY_TO_STRING(ARRAY(SELECT chr((65 + round(random() * 25)) :: integer)
FROM GENERATE_SERIES(1, 5)), '')
UNION
SELECT num + 1,
MD5(RANDOM()::TEXT),
ARRAY_TO_STRING(ARRAY(SELECT chr((65 + round(random() * 25)) :: integer)
FROM GENERATE_SERIES(1, 5)), '')
FROM rand
WHERE num < 5
)
SELECT * FROM rand;
A typical result is shown below:
1 974ee059a1902e5ca1ec73c91275984b GYXYS
2 6cf5a974d5859eae23cdb9c310e3a3bf YFDPT
3 fa6be95eb720fe6f80c7c8fb6ba11171 YFDPT
4 fa54913b0bb43de0025b153fd71a5334 YFDPT
5 523fab9bdc6c4c51a89e0d901273fb69 YFDPT
Now, the interesting thing is the ARRAY_TO_STRING.
The fact that the last 4 are identical is not a coincidence. If I put
100 in the GENERATE_SERIES, I still get the same result, the first and
second records are different, but ALL subsequent instances of the
ARRAY_TO_STRING are identical! You can test this on the fiddle!
Now, I'm puzzled by this, since the MD5 records are ALWAYS different.
I would be grateful if anybody could explain this - I need the
ARRAY_TO_STRING, because an MD5 cannot be guaranteed to have at least
5 letters.
Should you require any further information, please don't hesitate to contact me.
TIA and rgs,
Pól...