The ARRAY_TO_STRING() function in PostgreSQL

The ARRAY_TO_STRING() function in PostgreSQL is used to convert an array into a string by concatenating its elements with a specified delimiter. Its syntax looks like this:

ARRAY_TO_STRING(array_expression, delimiter)

For example, if you have an array of numbers [-10, 20, -30, 0, -40], and you want to concatenate its elements into a string with a comma delimiter, you can use ARRAY_TO_STRING() like this:

SELECT ARRAY_TO_STRING(ARRAY[-10, 20, -30, 0, -40], ',');

Output:
-10,20,-30,0,-40

The following PostgreSQL query will generate a random string of characters, each character being an uppercase letter (A-Z), separated by commas. The length of the string will be 10 characters, as specified by the generate_series function.

SELECT 
 ARRAY_TO_STRING(ARRAY(SELECT chr((65 + (random() * 25))::int) 
FROM 
 generate_series(1, 10)), ',');

Output:
N,F,B,F,R,B,G,U,T,W

This PostgreSQL query will generate a random string of characters, each character being an uppercase letter (A-Z), separated by commas. The length of the string will be randomly determined between 0 and 10 characters.

SELECT 
 ARRAY_TO_STRING(ARRAY(SELECT chr((65 + (random() * 25))::int) 
FROM 
 generate_series(1, (10*random())::int)), ',');

Output (multiple runs of the query):
D,R,R,H
I,B,W,G,K,N
E,B,M

This PostgreSQL query will generate a random string of characters with the specified conditions. Each character will be randomly selected to be either an uppercase letter (A-Z) or a lowercase letter (a-z), and the length of the string will be randomly determined between 0 and 10 characters. The characters will be separated by commas.

SELECT ARRAY_TO_STRING(ARRAY(
 SELECT 
 CASE 
 WHEN random() < 0.5 THEN chr((65 + (random() * 25))::int) -- uppercase letter (A-Z)
 ELSE chr((97 + (random() * 25))::int) -- lowercase letter (a-z)
 END
 FROM generate_series(1, (10*random())::int)
), ',');

Output (multiple runs of the query):
L,r,D,H,P,g
h,t,K,d,n,E,d,J,j
h,w,J,D,I
j,o

To generate a random string of characters in PostgreSQL, but without any delimiter, you can modify the query as follows:

SELECT ARRAY_TO_STRING(ARRAY(
    SELECT 
        CASE 
            WHEN random() < 0.5 THEN chr((65 + (random() * 25))::int) -- uppercase letter (A-Z)
            ELSE chr((97 + (random() * 25))::int) -- lowercase letter (a-z)
        END
    FROM generate_series(1, (10*random())::int)
), '') AS random_string;

Also, you can use a Common Table Expression (CTE) with the WITH clause to write the query.

In this query, a common table expression (CTE) named random_chars is created, which generates random characters. Then, in the main part of the query, the ARRAY_TO_STRING() function is used to create a string from the array of characters taken from the random_chars CTE, without using a delimiter.

WITH random_chars AS (
    SELECT
        CASE 
            WHEN random() < 0.5 THEN chr((65 + (random() * 25))::int) -- uppercase letter (A-Z)
            ELSE chr((97 + (random() * 25))::int) -- lowercase letter (a-z)
        END AS random_char
    FROM generate_series(1, (10*random())::int)
)
SELECT ARRAY_TO_STRING(ARRAY(SELECT random_char FROM random_chars), '') AS random_string;

Output (multiple runs):
w
xQlfX
KJIGnqfG

[e-mail]