Combine Values with CONCAT

You've just built a query that returns a list of customers, but the first_name and last_name come back in separate columns, and your boss wants a single, human-readable greeting. Manually concatenating values in your application's code is wasteful and error-prone, especially when you also need to handle blanks, add separators, or clean up formatting. PostgreSQL gives you a toolkit of string functions — led by CONCAT — that lets you combine, transform, and polish text directly in the database, keeping your app layer lean and your output exactly as your users expect.

The problem this lesson solves

Real-world data rarely arrives in the shape you need. You might have a users table with split name fields, a products table with brand and model columns, or a logs table with separate date and message parts. Without a solid way to merge these values, your application code ends up doing the busywork: looping through rows, concatenating strings, and hunting for nulls. That approach is slow, hard to maintain, and easy to get wrong — especially when a NULL sneaks in and silently turns your entire string into null.

PostgreSQL's string functions solve this by moving the transformation into the query layer. With CONCAT and its companions, you can merge columns, add separators, trim whitespace, and even change case — all with a few readable function calls. This lesson shows you how to combine values cleanly, when each function shines, and how to avoid the classic pitfalls that trip up beginners.

Core concept / mental model

Think of a database query as a pipeline: raw rows go in, and the SELECT list determines what comes out. String functions act as transformers along that pipeline, taking one or more inputs and producing a single output string. CONCAT is the simplest of these: it takes two or more arguments and joins them end-to-end, treating NULL as an empty string.

But CONCAT is just the starting point. PostgreSQL's string toolkit includes:

• CONCAT_WS — concatenates with a separator between each value, ignoring NULLs.
• || operator — the older, stricter concatenation method that propagates NULLs.
• FORMAT — for building strings with a pattern, similar to sprintf.
• Utility functions like LENGTH, UPPER, LOWER, TRIM, and SUBSTRING to adjust and inspect the result.

Mental model: imagine you're assembling a sentence from word cards. CONCAT just stacks cards side-by-side; CONCAT_WS slips a small connector (like a hyphen or space) between them; || is like a strict glue that refuses to work if any card is missing; and FORMAT gives you a template with blanks to fill in.

How it works step by step

1. Understand the basic syntax: CONCAT(value1, value2, ...) — each argument is converted to text, NULL is treated as an empty string, and the results are joined. For example, CONCAT('Hello', ' ', 'World') yields 'Hello World'.

2. Add separators with CONCAT_WS: When you need a consistent delimiter (a space, comma, slash), CONCAT_WS(sep, val1, val2, ...) inserts sep between every argument. Crucially, it skips NULL values entirely, so CONCAT_WS(' ', 'Jane', NULL, 'Doe') returns 'Jane Doe'.

3. Know when to use ||: The || operator is the raw concatenation workhorse. It's more flexible for non-text types, but if any operand is NULL, the whole result becomes NULL. Use it when you want to fail fast or when performance is paramount (it's slightly faster than CONCAT).

4. Leverage case and trimming functions: Often you need to clean up before you combine. Functions like UPPER, LOWER, INITCAP, and TRIM let you standardize text. For instance, to create a title-cased full name from messy inputs, combine INITCAP with CONCAT_WS.

5. Handle locale-aware formatting with FORMAT: The FORMAT function accepts a format string with %s placeholders and substitutes arguments. This is ideal for building complex messages like email templates or log lines.

Hands-on walkthrough

Let's start with a sample users table:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  city VARCHAR(50)
);

INSERT INTO users (first_name, last_name, city) VALUES
('John', 'Doe', 'New York'),
('Jane', 'Smith', 'Los Angeles'),
('Mike', NULL, 'Chicago'),
(NULL, 'Brown', 'Houston');

Example 1: Basic CONCAT

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

Expected output:

   full_name
---------------
 John Doe
 Jane Smith
 Mike
 Brown
(4 rows)

Notice how NULL values are treated as empty strings, so Mike and Brown appear without extra spaces — CONCAT skips the blank.

Example 2: Using CONCAT_WS with a separator

SELECT CONCAT_WS(' ', first_name, last_name, city) AS info
FROM users;

Expected output:

          info
-------------------------
 John Doe New York
 Jane Smith Los Angeles
 Mike Chicago
 Brown Houston
(4 rows)

CONCAT_WS inserts the space only between non-NULL values, so you never get double spaces.

Example 3: The || operator and NULL behavior

-- This returns NULL for NULL-containing rows
SELECT first_name || ' ' || last_name AS full_name
FROM users;

Expected output:

   full_name
---------------
 John Doe
 Jane Smith


(4 rows)

Rows 3 and 4 are NULL because || propagates NULL. If you need those rows to be non-null, use CONCAT instead.

Example 4: Combining with UPPER and TRIM

SELECT CONCAT_WS(' ', UPPER(first_name), UPPER(last_name)) AS shout_name
FROM users
WHERE first_name IS NOT NULL;

Expected output:

  shout_name
---------------
 JOHN DOE
 JANE SMITH
 MIKE
(3 rows)

Here we combine UPPER transformation with the flexibility of CONCAT_WS to produce a clean, all-caps name.

Pro tip: If you ever need to concatenate a large number of columns or complex expressions, wrap them in a subquery or use FORMAT for readability. CONCAT and CONCAT_WS are variadic, but keeping your expression readable is more important than squeezing every character of SQL.

Compare options / when to choose what

Different tools serve different situations. Here’s a quick comparison:

When to choose what:

• Use CONCAT for quick, human-readable merges where a missing value should just disappear.
• Use CONCAT_WS whenever you need a separator — it’s the safest way to avoid double separators from NULL.
• Use || in performance-critical paths or when you want NULL to signal missing data.
• Use FORMAT for building structured strings like URLs, messages, or JSON snippets.

Troubleshooting & edge cases

The dreaded NULL propagation

If you use || with a NULL and get an unexpected NULL result, the fix is to switch to CONCAT or CONCAT_WS. This is the most common mistake for beginners.

-- Problem
SELECT NULL || 'abc';  -- returns NULL
-- Solution
SELECT CONCAT(NULL, 'abc');  -- returns 'abc'

Double separators

When building a full name with first_name || ' ' || last_name, a missing middle name can leave two spaces. Use CONCAT_WS to avoid this:

SELECT CONCAT_WS(' ', first_name, middle_name, last_name) FROM users;

Number to string conversion

CONCAT automatically converts numbers to text, but if you mix types with ||, be careful — PostgreSQL will convert implicitly, but you might get unexpected results with certain locale formatting. Use CAST or TO_CHAR for explicit control.

-- Works, but uses implicit conversion
SELECT 'Order ' || order_id FROM orders;
-- Better: explicit and flexible
SELECT CONCAT('Order ', TO_CHAR(order_id, 'FM9999')) FROM orders;

Performance considerations

String concatenation in SQL is generally fast, but if you're doing heavy procedural work, consider moving it to a computed column or a view. Indexing a concatenated expression can help with searches:

CREATE INDEX idx_users_full_name ON users ((CONCAT_WS(' ', first_name, last_name)));

Pro tip: For changing case in a locale-aware way, use COLLATE with INITCAP to respect the database's locale settings. LOWER and UPPER are safe for most purposes, but Turkish locales can behave unexpectedly.

What you learned & what's next

You now have a solid grasp of combining values with CONCAT and string functions in PostgreSQL. You can tell when to reach for CONCAT versus CONCAT_WS or the || operator, you understand how to handle NULL gracefully, and you know how to add case transformations and formatting to produce clean, user-ready strings. These skills are the foundation for building more complex queries that generate reports, fill in emails, or construct paths.

You also learned how to troubleshoot the classic NULL propagation issue and why CONCAT_WS is your friend when separators are involved. The next lesson in this track will likely dive into pattern matching with LIKE and regex, where you'll use these string skills to search and parse text even more precisely.