1

We want a function that given an argument that is three bytes of type bytea (generated by the function gen_random_bytes of the pgcrypto extension), the function returns a random 6-digit integer (between 0 & 999999 inclusive). The 6-digit integer should preserve the randomness given by the argument passed to the function.

ma11hew28
  • 121,420
  • 116
  • 450
  • 651
  • Possible duplicate of [What's the easiest way to represent a bytea as a single integer in PostgreSQL?](https://stackoverflow.com/questions/17208945/whats-the-easiest-way-to-represent-a-bytea-as-a-single-integer-in-postgresql) – JJJ Jun 26 '17 at 16:04

2 Answers2

1

I might be overcomplicating, but I need to make sure that I have exactly n numeric digits in the string. Repetition of numbers is allowed.

SELECT string_agg(shuffle('0123456789')::char, '')
FROM generate_series(1, 6);

With the shuffle function provided in another answer that I copied here for convenience

create or replace function shuffle(text)
returns text language sql as $$
    select string_agg(ch, '')
    from (
        select substr($1, i, 1) ch
        from generate_series(1, length($1)) i
        order by random()
        ) s
$$;
Christiaan Westerbeek
  • 10,619
  • 13
  • 64
  • 89
0

In case of 3 bytes, take 6 last chars, prepend 'x', convert to bitstring and then to int:

select ('x' || right(gen_random_bytes(3)::text, 6))::bit(24)::int;

More details are in similar question: What's the easiest way to represent a bytea as a single integer in PostgreSQL?

Nick
  • 2,423
  • 13
  • 21