PostgreSQL: How to use concatenation operator (||) to combine strings

Introduction

String concatenation is a fundamental operation in any database system, and PostgreSQL uses the double pipe || as a concatenation operator to merge strings together efficiently. This tutorial explores the versatile applications of the || operator in PostgreSQL.

Basic Concatenation

To begin with, let’s look at the simplest form of string concatenation. Given two strings, ‘Hello’ and ‘World’, you can combine them into a single string, ‘HelloWorld’, using the || operator:

SELECT 'Hello' || 'World';

You will receive the combined string as the result:

HelloWorld

Concatenating with Spaces

More often, you’ll want a space between words. You can include the space as part of your string literal:

SELECT 'Hello' || ' ' || 'World';

The result would be:

Hello World

Using Concatenation with Columns

It’s common to use the || operator to combine columns in a result set. If your database has a table ‘users’ with ‘first_name’ and ‘last_name’ columns, you can create a full name by concatenating the two:

SELECT first_name || ' ' || last_name AS full_name FROM users;

Handling NULL Values

Concatenation can become tricky when NULL values are involved, as any string combined with NULL using the || operator will result in NULL. Use the COALESCE function to handle NULL values gracefully:

SELECT first_name || ' ' || COALESCE(last_name, '') AS full_name FROM users;

This ensures that if last_name is null, an empty string will take its place.

Concatenating Numbers and Strings

Strings can be concatenated with numbers implicitly without the need for casting:

SELECT 'Your order number is ' || 25 || '.' AS order_confirmation;

In this case, PostgreSQL will automatically convert the integer 25 to a string before concatenation.

Concatenating with Aggregate Functions

PostgreSQL allows you to concatenate strings across rows using the STRING_AGG function:

SELECT STRING_AGG(name || '(', ')') FROM products;

This concatenates product names and adds parentheses around them.

Advanced Concatenation Scenarios

You can also use the || operator to build dynamic SQL queries within a plpgsql function, or to concatenate text within array processing.

Creating Conditions with Concatenation

Dynamic SQL in PostgreSQL can benefit greatly from the || operator:

CREATE OR REPLACE FUNCTION dynamic_query(search_term TEXT) RETURNS void AS $$
BEGIN
  EXECUTE 'SELECT * FROM products WHERE name LIKE ''%' || search_term || '%'';';
END;
$$ LANGUAGE plpgsql;

In this function, the search term is concatenated into a dynamic query within the function’s EXECUTE statement.

Concatenating Arrays

Strings can also be concatenated within arrays. To concatenate a prefix with every element of an array:

SELECT ARRAY(SELECT 'Item: ' || s FROM unnest(ARRAY['one', 'two', 'three']) AS s);

This code produces an array of strings prefixed with ‘Item: ‘.

Conclusion

The || operator in PostgreSQL is a powerful tool for combining strings. From simple concatenations to its use in dynamic SQL and array operations, understanding || expands your ability to manipulate and display data in custom formats. Test your knowledge with the given examples and look for ways to integrate string concatenation in your PostgreSQL queries!