PostgreSQL: String Concatenation

I’ve spent countless hours building user-facing reports, email templates, and data exports where I needed to combine first and last names, addresses, or formatted messages. When you’re pulling data from multiple columns and need to present it as a single, readable string, PostgreSQL’s string concatenation tools become your best friends.

Whether you’re creating full names from first and last names, building formatted addresses, generating email templates, or just combining text fields, you’ll quickly realize that splitting work across multiple columns is clean for storage, but messy for display. That’s where concatenation comes in—it lets you blend your data into meaningful, human-readable strings.

Quick Answer: Use || Operator or CONCAT()

To concatenate strings:

SELECT
    first_name,
    last_name,
    first_name || ' ' || last_name as full_name,
    CONCAT(first_name, ' ', last_name) as concat_method
FROM users
WHERE first_name IS NOT NULL
LIMIT 5;

Returns:

first_name | last_name | full_name    | concat_method
------------|-----------|--------------|---------------
John        | Doe       | John Doe     | John Doe
Mary        | Smith     | Mary Smith   | Mary Smith

Both the || operator and CONCAT() function combine strings. The || operator is what most PostgreSQL developers reach for first, but both work equally well.

How It Works

Here’s what happens under the hood: the || operator (pronounced “pipe pipe”) is PostgreSQL’s string concatenation operator. When you write first_name || ' ' || last_name, PostgreSQL takes the value from first_name, appends a space, then appends last_name. It works left-to-right, chaining multiple values together.

The CONCAT() function does the same thing but uses function syntax instead of an operator. Both produce identical results. PostgreSQL also automatically converts non-string types to strings during concatenation—if you do 'ID: ' || emp_id where emp_id is an integer, PostgreSQL converts the integer to text first.

The key thing to understand is that || treats NULL differently than some other operations. If any operand is NULL, the entire result becomes NULL. This is SQL standard behavior, but it can surprise you. If you’re concatenating fields that might be NULL, you’ll need to handle that explicitly with COALESCE() or NULLIF().

Different Approaches & When to Use Them

Approach 1: || Operator (Most Common)

The || operator is what you’ll see in most PostgreSQL codebases. It’s simple, readable, and chains multiple strings together:

SELECT
    emp_id,
    first_name,
    last_name,
    first_name || ' ' || last_name as full_name
FROM employees
WHERE first_name IS NOT NULL
ORDER BY last_name;

I use this approach most of the time because it’s concise and immediately clear what you’re doing. You’re literally saying “take this value, add this, add that.” It reads like English.

Approach 2: CONCAT Function for Explicit Syntax

Some developers prefer the CONCAT() function because it’s more explicit and aligns with how other programming languages handle string concatenation:

SELECT
    emp_id,
    first_name,
    middle_name,
    last_name,
    CONCAT(first_name, ' ', middle_name, ' ', last_name) as full_name
FROM employees
WHERE first_name IS NOT NULL;

This approach feels more like traditional function calls. The downside is you have more parentheses and explicit function syntax. I find myself using || more in SQL, but if you’re combining many values, CONCAT() can look cleaner.

Approach 3: CONCAT_WS (With Separator) for Cleaner NULL Handling

CONCAT_WS() stands for “concatenate with separator” and is incredibly useful when you’re combining multiple fields that might be NULL:

SELECT
    emp_id,
    CONCAT_WS(' ', first_name, middle_name, last_name) as full_name,
    CONCAT_WS(', ', street_address, city, state, postal_code) as mailing_address
FROM employees;

The magic here is that CONCAT_WS() automatically skips NULL values. If someone doesn’t have a middle name, CONCAT_WS() won’t add an extra space. If there’s no state, it won’t add an extra comma. This is perfect for building flexible strings where some values might be missing. I use this constantly for address fields and name formatting.

Approach 4: FORMAT Function (Advanced) for Complex Patterns

The FORMAT() function gives you printf-style formatting, which is great when you need to build complex strings with specific patterns:

SELECT
    emp_id,
    FORMAT('%s %s', first_name, last_name) as full_name,
    FORMAT('Email: %s | Phone: %s', email, phone) as contact_info,
    FORMAT('Employee #%06d - %s', emp_id, UPPER(last_name)) as employee_label
FROM employees;

This is useful when you’re following a specific format pattern, like “Employee #000123 - SMITH”. It gives you more control over spacing and positioning than simple concatenation. I reach for this when building formatted reports or labels that need to follow a consistent pattern.

Approach 5: Build Complex Strings with Multiple Concatenations

Sometimes you’re combining concatenation with other functions to create complex display strings:

SELECT
    emp_id,
    INITCAP(first_name) || ' ' || INITCAP(last_name) as formatted_name,
    '<' || LOWER(email) || '>' as formatted_email,
    '[' || dept_name || ']' as formatted_department,
    'https://example.com/employee/' || emp_id as employee_url
FROM employees
WHERE email IS NOT NULL;

This approach combines concatenation with case conversion, URL building, and other formatting. It’s realistic because you’re rarely just concatenating raw values—you’re usually formatting them at the same time.

Real-World Example: Generate Email Notification Templates

Let me walk you through a practical scenario. You’re building an employee notification system that needs to send personalized emails. You’re pulling data from the employees table and need to generate subject lines and email bodies:

SELECT
    emp_id,
    email,
    CONCAT_WS(' ', first_name, last_name) as employee_name,
    'Hello ' || INITCAP(first_name) || ',' as greeting,
    'Congratulations! Your annual review for ' ||
    TO_CHAR(CURRENT_DATE, 'YYYY') || ' has been scheduled for ' ||
    review_date || '.' as email_subject,
    'Dear ' || INITCAP(first_name) || ',' || CHR(10) || CHR(10) ||
    'Your performance review is scheduled for ' || review_date ||
    ' with ' || manager_name || '.' || CHR(10) ||
    'Please prepare a summary of your accomplishments this year.' || CHR(10) ||
    'Review your goals at: https://example.com/reviews/' || emp_id as email_body
FROM employees
WHERE email IS NOT NULL
AND review_date IS NOT NULL
ORDER BY review_date;

Here you’re doing real work: combining multiple fields with INITCAP, adding dates with TO_CHAR, using CHR(10) to insert line breaks, and building clickable URLs with the employee ID. This is exactly what production code looks like—concatenation combined with formatting functions.

FAQ

Q: What happens when concatenating NULL values? A: With the || operator, if any operand is NULL, the entire result becomes NULL. For example: 'Hello ' || NULL || ' World' returns NULL. This is SQL standard behavior but can be frustrating. Use COALESCE() to provide default values: 'Hello ' || COALESCE(middle_name, '') || ' World'.

Q: What’s the difference between || and CONCAT()? A: The || operator returns NULL if any operand is NULL. The CONCAT() function treats NULL operands as empty strings and skips them. So CONCAT('Hello', NULL, 'World') returns ‘HelloWorld’, but 'Hello' || NULL || 'World' returns NULL. Use CONCAT() or CONCAT_WS() when you’re working with potentially NULL values.

Q: Which method is faster—|| or CONCAT()? A: Both are equally fast. PostgreSQL handles them with the same efficiency. Choose based on readability and NULL handling needs, not performance.

Q: How do I include line breaks in concatenated strings? A: Use CHR(10) for newline: 'Line 1' || CHR(10) || 'Line 2'. You can also use E'\n' with escape syntax: E'Line 1\nLine 2'.

Q: Can I concatenate non-string types like numbers or dates? A: Yes, PostgreSQL automatically converts them to text. For example: 'ID: ' || emp_id where emp_id is an integer works fine. PostgreSQL figures it out. However, be explicit with CAST if you want to control formatting: 'Date: ' || CAST(hire_date AS TEXT).

Q: How many values can I concatenate together? A: Theoretically unlimited, but practically, after about 10-15 values, consider breaking it into multiple columns or using a function. Very long concatenations become hard to read and maintain.

Q: When should I use CONCAT_WS instead of ||? A: Use CONCAT_WS() when you’re building strings from multiple fields that might contain NULLs. It automatically handles the separator and skips NULL values. Use || when you want simple, explicit concatenation of known values.

Q: Can I use concatenation in WHERE clauses to search? A: Yes, absolutely. WHERE first_name || ' ' || last_name LIKE '%John%' searches the concatenated full name. However, this won’t use indexes efficiently. It’s better to search on the original columns: WHERE first_name LIKE '%John%' OR last_name LIKE '%John%'.

Q: How do I concatenate values from different rows or aggregate them? A: Use STRING_AGG() to concatenate values across multiple rows: STRING_AGG(name, ', ') concatenates all names with commas. Perfect for building comma-separated lists.

Q: What’s the performance impact of heavy concatenation in SELECT? A: Negligible for typical operations. PostgreSQL is optimized for this. Even concatenating thousands of strings per query has minimal overhead. Don’t worry about optimization unless you’re doing millions of concatenations per query.

Common Errors & Solutions

Error: “Result is NULL when I expect a string” You probably concatenated NULL values using ||. Remember: || returns NULL if any operand is NULL. Use COALESCE() to handle NULLs: COALESCE(middle_name, '') || ' ' treats NULL as an empty string. Or use CONCAT_WS() which skips NULLs automatically.

Error: “Unexpected output formatting or spacing” Check for extra spaces or missing separators. With ||, you control every character—if you need a space, add it explicitly: first_name || ' ' || last_name. Make sure you’re adding separators between every value.

Error: “Query is slow when concatenating many columns” If you’re concatenating hundreds of columns or very large text values, check that you’re not doing this repeatedly in a loop. Use a CTE or subquery to do the concatenation once, then reuse it.

Performance Tips

  • Fast operation: String concatenation is very fast in PostgreSQL. Use it freely without worrying about optimization.
  • Avoid || in WHERE clauses: While it works, WHERE concatenated_string = 'value' won’t use indexes. Search on the original columns instead.
  • Use CONCAT_WS for NULL handling: It’s slightly more efficient than using multiple COALESCE() calls.
  • With string aggregation: STRING_AGG() is optimized for building comma-separated lists from many rows. It’s very fast even with thousands of rows.
  • In indexes: You can create computed indexes on concatenated values if you search them frequently: CREATE INDEX idx_full_name ON employees (first_name || ' ' || last_name).

Wrapping Up

String concatenation is one of those fundamental operations you’ll use constantly in PostgreSQL. Whether you choose || for simplicity, CONCAT_WS() for NULL safety, or FORMAT() for complex patterns, the key is understanding how each handles edge cases like NULL values. Use || when you’re combining known values, CONCAT_WS() when dealing with potentially missing data, and FORMAT() when you need precise formatting control. All three are fast and reliable—pick the one that makes your code most readable.