Concatenation with || operator in PostgreSQL

Introduction

Concatenation with || operator joins two or more text pieces into one string. It helps combine words or data easily.

You want to combine first and last names into a full name.

You need to add a greeting before a user's name.

You want to merge city and country into one location string.

You want to create a full address from separate parts.

Syntax

PostgreSQL

string1 || string2 || ... || stringN

The || operator joins text values without adding spaces automatically.

Use single quotes for text literals, e.g., 'Hello' || ' ' || 'World'.

Examples

Joins three strings with a space in the middle to form 'Hello World'.

PostgreSQL

'Hello' || ' ' || 'World'

Combines first_name and last_name columns with a space between.

PostgreSQL

first_name || ' ' || last_name

Creates a descriptive location string from city and country columns.

PostgreSQL

'City: ' || city || ', Country: ' || country

Sample Program

This query concatenates 'John' and 'Doe' with a space to show a full name.

PostgreSQL

SELECT 'John' || ' ' || 'Doe' AS full_name;

Important Notes

If any part is NULL, the whole result becomes NULL. Use COALESCE to avoid this.

The || operator works only with text types; cast other types if needed.

Summary

|| operator joins text pieces into one string.

It does not add spaces automatically; add them manually if needed.

Be careful with NULL values as they make the result NULL.