The CONCAT() function in PostgreSQL combines two or more strings into a single string. Unlike the || operator, CONCAT treats NULL values as empty strings, making it more convenient for concatenating columns that might contain NULLs.
Syntax
CONCAT(string1, string2 [, stringN ...])
Example usage
-- Basic concatenation
SELECT CONCAT('Hello', ' ', 'World') AS greeting;
-- Result: 'Hello World'
-- Concatenating with NULL values
SELECT CONCAT('Name: ', NULL, ' Smith') AS result;
-- Result: 'Name: Smith' (NULL treated as empty string)
-- Concatenating multiple columns
SELECT CONCAT(first_name, ' ', middle_name, ' ', last_name) AS full_name
FROM users;
-- Mixed data types (automatic conversion)
SELECT CONCAT('Order #', order_id, ' - Total: $', total_amount) AS order_info
FROM orders;
-- Building email addresses
SELECT CONCAT(username, '@', domain) AS email
FROM user_accounts;
Common issues
- Type conversion: CONCAT automatically converts non-string types, but be aware of how different types are represented.
- Separator handling: Unlike CONCAT_WS, you must manually add separators between values.
- Performance: For large-scale concatenations, consider the overhead of function calls vs the || operator.
Best practices
- Use CONCAT() when NULL values might be present and you want them treated as empty strings.
- For concatenating with a common separator, use CONCAT_WS() instead for cleaner code.
- When concatenating many row values into a single string, use string_agg() aggregate function.
- For performance-critical code with no NULLs, the || operator may be slightly faster.
Frequently Asked Questions
Q: How does CONCAT handle NULL values?
A: CONCAT treats NULL values as empty strings, so CONCAT('Hello', NULL, 'World') returns 'HelloWorld'. This differs from the || operator which returns NULL if any operand is NULL.
Q: Can CONCAT handle different data types?
A: Yes, CONCAT automatically converts arguments to text. You can concatenate integers, dates, booleans, and other types without explicit casting.
Q: What's the difference between CONCAT and CONCAT_WS?
A: CONCAT_WS (Concatenate With Separator) allows you to specify a separator that will be inserted between all arguments, making it cleaner for joining values with a common delimiter.
Q: Is there a limit to how many arguments CONCAT can accept?
A: There's no specific limit, but practical limits depend on PostgreSQL's function call limits and available memory. For very large concatenations, consider alternative approaches.
Q: Should I use CONCAT or the || operator?
A: Use CONCAT when you want NULL-safe concatenation (NULLs treated as empty strings). Use || when you want NULLs to propagate (entire result becomes NULL if any part is NULL).
