The || operator in PostgreSQL is used for string concatenation, allowing you to combine two or more text values into a single string. It's one of the most commonly used operators for text manipulation in SQL queries.
Syntax
string1 || string2 [|| string3 ...]
Example usage
-- Basic concatenation
SELECT 'Hello' || ' ' || 'World' AS greeting;
-- Result: 'Hello World'
-- Concatenating columns
SELECT first_name || ' ' || last_name AS full_name
FROM users;
-- Concatenating with NULL handling
SELECT 'User: ' || COALESCE(username, 'Anonymous') AS display_name
FROM users;
-- Building formatted strings
SELECT 'ID: ' || id || ', Name: ' || name AS formatted
FROM products;
Common issues
- NULL values: If any operand is NULL, the entire result becomes NULL. Use COALESCE() to handle NULLs.
- Type conversion: Non-string types need explicit casting when concatenating.
- Performance: For concatenating many strings, consider using CONCAT() or string aggregation functions.
Best practices
- Use COALESCE() or NULLIF() to handle NULL values when concatenating.
- For concatenating many columns, the CONCAT() function can be more readable.
- When building complex strings, consider using format() for better readability.
- For concatenating values from multiple rows, use string_agg() instead.
Frequently Asked Questions
Q: What happens when I concatenate NULL with a string using ||?
A: The entire result becomes NULL. To avoid this, wrap potentially NULL values with COALESCE(column, '') or use the CONCAT() function which treats NULL as an empty string.
Q: How do I concatenate non-string types like integers or dates?
A: You need to cast them explicitly: 'Order ' || order_id::text or use CONCAT() which handles type conversion automatically.
Q: Is || operator the same as CONCAT() function?
A: They're similar but handle NULLs differently. The || operator returns NULL if any operand is NULL, while CONCAT() treats NULL as an empty string.
Q: Can I use || to concatenate arrays?
A: Yes, || can also concatenate arrays or append elements to arrays in PostgreSQL, but the behavior is different from string concatenation.
Q: Which is faster, || or CONCAT()?
A: The || operator is generally slightly faster for simple concatenations, but the performance difference is negligible in most cases. Choose based on NULL handling requirements.
