DEV Community

Dmitry Romanoff
Dmitry Romanoff

Posted on

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)
Enter fullscreen mode Exit fullscreen mode

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], ',');
Enter fullscreen mode Exit fullscreen mode

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)), ',');
Enter fullscreen mode Exit fullscreen mode

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)), ',');
Enter fullscreen mode Exit fullscreen mode

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)
), ',');
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Output (multiple runs):
w
xQlfX
KJIGnqfG

ask_dima@yahoo.com

API Trace View

How I Cut 22.3 Seconds Off an API Call with Sentry

Struggling with slow API calls? Dan Mindru walks through how he used Sentry's new Trace View feature to shave off 22.3 seconds from an API call.

Get a practical walkthrough of how to identify bottlenecks, split tasks into multiple parallel tasks, identify slow AI model calls, and more.

Read more →

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay