PostgreSQL CONCAT Function

PostgreSQL CONCAT Function

The CONCAT function in PostgreSQL is used to combine multiple strings into one. It automatically handles NULL values by treating them as empty strings ('').

1. Basic Syntax

SELECT CONCAT(string1, string2, ...);

• Accepts multiple string arguments.
• Returns a single concatenated string.

Example: Simple Concatenation

SELECT CONCAT('Hello', ' ', 'World');

✅ Output: 'Hello World'

2. Concatenating Table Columns

You can concatenate columns from a table.

Example: Combine First and Last Name

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

✅ Output:

first_name	last_name	full_name
John	Doe	John Doe
Jane	Smith	Jane Smith

3. Handling NULL Values

• CONCAT treats NULL as an empty string ('').
• Use CONCAT_WS (explained later) if you want to ignore NULL values.

Example: NULL Handling

SELECT CONCAT('User: ', NULL, ' Active');

✅ Output: 'User: Active' (NULL is treated as '')

4. Using || (Alternative String Concatenation)

PostgreSQL allows || as a shorthand for CONCAT.

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

✅ Output: 'Hello World'

Example: Concatenating Columns

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

✅ Works the same as CONCAT but returns NULL if any column is NULL.

5. Using CONCAT_WS (Concatenation with Separator)

• CONCAT_WS(separator, string1, string2, ...)
• Ignores NULL values (useful when some columns may be NULL).

Example: Join Name with Comma

SELECT CONCAT_WS(', ', first_name, middle_name, last_name) AS full_name
FROM employees;

✅ Output:

first_name	middle_name	last_name	full_name
John	A.	Doe	John, A., Doe
Jane	NULL	Smith	Jane, Smith

🔹 Unlike CONCAT, NULL values are ignored!

6. Concatenating Numbers and Dates

PostgreSQL automatically converts numbers and dates to text when using CONCAT.

Example: Concatenating Numbers

SELECT CONCAT('Order #', 123, ' - Total: $', 49.99);

✅ Output: 'Order #123 - Total: $49.99'

Example: Concatenating Dates

SELECT CONCAT('Today is ', CURRENT_DATE);

✅ Output: 'Today is 2025-02-01' (example date)

7. Concatenating with GROUP BY (List Aggregation)

To concatenate multiple row values into a single string, use STRING_AGG().

Example: List Employee Names in One Row

SELECT STRING_AGG(first_name, ', ') AS all_names
FROM employees;

✅ Output: 'John, Jane, Alice, Bob'

8. Summary

Use Case	Query	Notes
Basic Concatenation	SELECT CONCAT('Hello', ' ', 'World');	'Hello World'
Concatenating Columns	SELECT CONCAT(first_name, ' ', last_name) FROM employees;	Joins name columns
Handling NULLs	SELECT CONCAT('User: ', NULL, ' Active');	NULL treated as ''
**Alternative (`		`)**
Using Separator (CONCAT_WS)	SELECT CONCAT_WS(', ', first_name, middle_name, last_name);	Ignores NULL values
Concatenating Numbers/Dates	SELECT CONCAT('Order #', 123, ' - ', CURRENT_DATE);	Automatically converts numbers and dates
Group Concatenation	SELECT STRING_AGG(first_name, ', ') FROM employees;	Merges multiple rows into one

Would you like examples for a specific use case? 🚀