postgresqlrangedata-generation

Postgre: generate uniq random string from range


I want to generate a random text value from a range and one that is not already present in the table.

For examle I have range from 'abcdef' to 'abcdej'.

I have a tableA(id varchar(6), someData) where id must be unique.

I want to generate text value from range like from list ('abcdef','abcdeg','abcdeh','abcdei','abcdej') and check if it's not in tableA.

Is there any way to do it? (preferably without additional procedures)

Upd:

  1. generated value supposed to become a new id
  2. As an example: range is from 'FOOBARA' to 'FOOBARZ'. there are already exists values from from 'FOOBARB' to 'FOOBARV' in tableA. Result must be randomly selected from list:'FOOBARA','FOOBARW','FOOBARX','FOOBARY','FOOBARZ'.
  3. In practice, the range, and as a result, the string will be 8 or 15 characters long, so there is no need for universality in the length context. It is assumed that the boundaries of the range are specified correctly and have the same length.

Solution

  • This is a bad solution to the problem because it will take a very long time and eat up a lot of resources, but if you have a similar problem on a smaller scale, this may suit you:

    WITH RECURSIVE Tuple AS
    (
      SELECT
        CAST(charr AS Text) AS Tuple
       ,1 AS TupleLength
      FROM (select regexp_split_to_table('QWERTYUIOPASDFGHJKLZXCVBNM1234567890', '') charr) all_chars
    
      UNION ALL
    
      SELECT
        T.Tuple || E.charr
       ,TupleLength + 1
      FROM Tuple T
      INNER JOIN (select regexp_split_to_table('QWERTYUIOPASDFGHJKLZXCVBNM1234567890', '') charr) E ON E.charr >= RIGHT(T.Tuple,1)
      WHERE TupleLength < :length
    )
    SELECT Tuple
    FROM Tuple
    where TupleLength = :length /* between leftrange and rightrange  */ /* and not n table */
    ORDER BY TupleLength desc,Tuple;
    

    It remains only to choose a random value from the list.

    Thanks Addell El-haddad.