Converting PostgreSQL Arrays to Strings

As a full stack developer and database engineer, I work extensively with the PostgreSQL database system – widely regarded as one of the most advanced open source databases with professional-grade capabilities. One of my favorite PostgreSQL features is the excellent native support for arrays – data types allowing storage of multiple homogeneous values in a single column.

Arrays have many database applications like storing related data together, improving normalization, and even optimizing queries by avoiding expensive joins. PostgreSQL has a very mature implementation that supports indexes, complex querying and aggregation, and a myriad of supporting functions and operators that unlock many possibilities.

One handy array function that every Postgres developer should know is array_to_string(). As the name suggests, it concatenates all elements of an array into a single string separated by a provided delimiter character. It has some nice conveniences for things like handling null values that make array manipulation tasks much simpler.

Let‘s walk through some applied examples of using array_to_string() for efficient string building operations that are common tasks in real world software. Understanding use cases like these has helped me immensely in my career developing database-backed applications.

Why Arrays and String Manipulation Matter

But first – why do arrays and string operations matter in database systems? Isn‘t that something better left to application code?

In many cases yes – but having strong manipulations capabilities built into the database layer unlocks immense gains:

• Performance: Native functions execute much faster with lower data transfer overhead compared to transferring entire result sets to clients. String operations can leverage advanced SQL optimizations.

• Simplicity: Cleaner application code not having to iterate through result sets. Complex logic abstracted into database functions.

• Consistency: Central logic living in database avoids bugs spreading across separate layers.

• Platform Interoperability: Functions work identically across diverse client languages and frameworks, not having to reimplement anything.

In short, you want your database doing the heavy lifting it‘s optimized for as much as possible!

Now let‘s see some realistic array_to_string() examples and learn some best practices along the way…

Concatenating Array Elements

The most basic usage of array_to_string() accepts an array and delimiter character.

For example, concatenating integers from an array with comma separators:

SELECT array_to_string(ARRAY[1, 2, 3, 4, 5], ‘,‘);

Result: 1,2,3,4,5

You could build more complex strings like piping-delimited names:

SELECT array_to_string(ARRAY[‘John‘, ‘Jane‘, ‘Bob‘], ‘|‘);

Result: John|Jane|Bob

But more exciting is what happens when you consider real world data with things like duplicate values, nulls, and varying data types – let‘s walk through some examples.

Handling Null Array Elements

Null values are tricky in string manipulations. Thankfully, array_to_string() accepts an optional third parameter for replacing nulls in the array with a placeholder value:

SELECT array_to_string(ARRAY[1, null, 2, null], ‘,‘, ‘0‘); 

Result: 1,0,2,0 

This technique is perfect for concatenating array data that may contain sparse elements.

Arrays Can Store Any Data Type

A lesser known feature of PostgreSQL arrays is that a single array can store mixed data types. This allows very flexible representations:

SELECT array_to_string(ARRAY[1, ‘2‘, null, 3.5, true], ‘; ‘);

Result: 1; 2; ; 3.5; true

Notice I used a semicolon delimiter here – array_to_string() handles the type conversions automatically!

String Concatenation vs Aggregation

A common question is when to use array_to_string() versus string aggregation functions that concatenate across rows rather than array elements.

For example, string_agg() accepts a column and delimiter just like array_to_string(), but aggregates multiple rows:

SELECT string_agg(name, ‘, ‘) 
FROM users;

Result: John, Jane, Bob

The approaches serve different purposes. I like to think of array_to_string() joining elements related in some way (like names for people) while string aggregation operates on unrelated rows.

But they can be combined together for some useful effects!

Use Case 1: Full Text Search Integration

A classic application of string manipulation is preparing delimited data for querying in full text search. PostgreSQL has amazing built-in text search capabilities that work even on JSON documents stored in arrays!

Let‘s walk through an example schema that stores tags arrays for enumerating topics related to content:

CREATE TABLE documents (
   id serial PRIMARY KEY,
   title text NOT NULL,
   tags text[]  
);

INSERT INTO documents (title, tags) VALUES
(‘PostgreSQL Guide‘,     ‘{postgresql, database, open-source}‘),
(‘Linux Tutorial‘,       ‘{linux, operating-system, unix}‘),   
(‘JS Tips and Tricks‘, ‘{javascript, web, front-end}‘); 

Now I want to perform full text search matching documents by tags. Unfortunately the built-in text search operators don‘t work directly on arrays. This a perfect application for array_to_string() to prepare a custom string field to expose to full text search:

SELECT id, 
  array_to_string(tags, ‘ | ‘) AS tag_list  
FROM documents;

Which results in computed columns like:

   id |                        tag_list
-----+--------------------------------------------------------
   1 | postgresql | database | open-source
   2 | linux | operating-system | unix

Next I can create a text search index on this computed array column:

CREATE INDEX documents_tags ON documents 
  USING GIN(to_tsvector(‘english‘, tag_list)); 

Allowing super fast queries like:

SELECT * FROM documents
WHERE to_tsvector(‘english‘, tag_list) @@ to_tsquery(‘unix‘); 

That relevance sorts documents by matching unix tags!

This is a pattern I implement all the time for search functionality while storing arrays cleanly in the raw data.

Use Case 2: Generate Calculated Columns

Business reporting commonly requires ad-hoc manipulation of fields to present user-friendly views – without altering the core tables. PostgreSQL views are perfect for such calculated "virtual" columns.

Let‘s look at an events table storing timestamps in ISO format:

CREATE TABLE events (
   id serial PRIMARY KEY,  
   page text NOT NULL,
   occurred_at timestamptz NOT NULL 
);

INSERT INTO events (page, occurred_at) VALUES
(‘/home‘, ‘2020-01-01 01:00:00‘),
(‘/about‘, ‘2020-01-01 02:30:00‘),
(‘/contact‘, ‘2020-01-01 04:15:00‘);

Now I want to select these events with an additional column formatting the timestamp nicely without modifying this underlying data.

A perfect application for array_to_string() to extract portions of the ISO timestamp into an array and recombine into a string:

SELECT 
   id,
   page,
   occurred_at,
   array_to_string(
      ARRAY[
         to_char(occurred_at, ‘Mon DD, YYYY‘),  
         to_char(occurred_at, ‘HH12:MI am‘)  
      ], 
      ‘ @ ‘
   ) AS neat_time
FROM events;

The output contains the cleanly formatted time I want for display:

 id | page  |       occurred_at       |       neat_time
----+-------+-------------------------+------------------------
  1 | /home | 2020-01-01 01:00:00+00 | Jan 01, 2020 @ 01:00 am
  2 | /about | 2020-01-01 02:30:00+00 | Jan 01, 2020 @ 02:30 am
  3 | /contact | 2020-01-01 04:15:00+00 | Jan 01, 2020 @ 04:15 am

This avoids permanently storing that redundant presentation data calculated from other fields. I can encapsulate this into a reusable view for easy access!

Alternative to ETL Processing

That example demonstrates a pattern I frequently use for lightweight "extract transform load" jobs in the database itself as opposed to external ETL tools. The string manipulations handle lightweight reformatting scenarios very well.