postgres shuffle random character

Question

I want to shuffle (ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890) and get substring from 1 to 6.

I have tried a lot but was not able to find a shuffle function in postgres

update tablename 
    set  secureno=substring(shuffle(ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890) from 1 for 6)

Answer 1

You can shuffle a string using this query:

select substr('ABCD', i, 1)
from generate_series(1, 4) i
order by random();

 substr 
--------
 B
 C
 A
 D
(4 rows)

Use it for your key (with 62 chars), get 6 chars and aggregate to string:

select string_agg(ch, '')
from (  
    select substr('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890', i, 1) ch
    from generate_series(1, 62) i
    order by random()
    limit 6
    ) s

You can create a function based on the algorithm if you often need this::

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
$$;

select left(shuffle('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890'), 6);

Answer 2

This would do it:

SELECT string_agg(c, '')
FROM (SELECT c
      FROM regexp_split_to_table(
              'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890',
              ''
           ) c
      ORDER BY random()
      LIMIT 6
     ) AS q;