How to concatenate columns in a PostgreSQL SELECT?

sql

join

best-practices

performance

byAlex Kataev·Nov 2, 2024

If you "just want some code", join columns in PostgreSQL by using the || operator. Using columns first_name and last_name in users as an example:

-- Quick and easy, like your favorite noodle recipe
SELECT first_name || ' ' || last_name AS full_name FROM users;

The output would be a concatenated full_name. But the specter of NULL can ruin our fun:

-- A bit like adding a safety net for the null-acrobats 
SELECT COALESCE(first_name, '') || ' ' || COALESCE(last_name, '') AS full_name FROM users;

The COALESCE function replaces NULL with a default, ensuring no NULL results.

Robust concatenation with CONCAT

To protect your code from NULL-induced interruptions, use the CONCAT function, which treats NULL like an empty string:

-- Your knight in shining armor against NULL 
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

The great quote brawl: Single vs Double

In the battle between single (' ') and double (" ") quotes, remember: single quotes handle string literals, double quotes deal with identifiers:

-- Just imagine the quotes are arm-wrestling for dominance
SELECT 'PartNumber:' || "PartID" from parts;

Single quotes here denote static text, and the double quotes identify a column. No need for a referee, PostgreSQL knows their strengths.

Size does matter: Avoiding data truncation

To dodge any size-related accidents, consider the length of your concatenated results. Beware of the VARCHAR(max_length) type, it might truncate your strings. If in doubt, TEXT type is your friend:

-- SQL's got your back!
ALTER TABLE users ALTER COLUMN full_name TYPE TEXT;

Keeping performance in check

Repeatedly concatenating large data can slow your roll. If concatenation is a common operation, consider pre-calculated or indexed computed columns:

-- String concatenation now on the fast lane!
CREATE INDEX idx_full_name on users ((first_name || ' ' || last_name));

Type, cast, and action!

Remember to match datatypes when concatenating. If you're trying to marry a numeric value with a string, you need to cast the numeric to text:

-- It's like SQL's version of a romantic comedy.
SELECT name || ' - ' || CAST(price AS TEXT) FROM products;

Nuances of NULL handling

In PostgreSQL, a NULL joined with any string is, ironically, NULL:

-- A bit like that one friend who never shows up for plans but RSVPs 'Maybe'
SELECT 'Hello, ' || NULL;  -- Result is NULL

But using COALESCE ensures NULL becomes "Mr. Reliable":

-- Who knew NULL could be so considerate?
SELECT 'Hello, ' || COALESCE(NULL, 'Unknown'); -- Result is 'Hello, Unknown'

Real-world utility

Consider scenarios that need concatenation:

Dynamic query generation: Combine variable inputs to brew SQL statements.
Table alterations and migration: Combine columns when altering table structure.
User-friendly representation: String pieces together for easy human consumption.

Look before you leap! Common traps

Avoid these common pitfalls:

Invisible NULL values: Can lead to unexpected voids in your result.
Quote mix-ups: Misused single or double quotes can cause syntax errors.
Neglecting performance: Heavy concatenation without optimization can weigh on your database.

explain-codes / Sql / How to concatenate columns in a PostgreSQL SELECT?

Linked

Mysql SELECT AS combine two columns into one



How create json format with group-concat mysql?



Mysql CONCAT returns NULL if any field contain NULL



How to concatenate strings of a string field in a PostgreSQL 'group by' query?



Optimal way to concatenate/aggregate strings

