Concatenation with || operator in PostgreSQL

Introduction

Concatenation with || operator joins two or more text pieces into one string. It helps combine words or data easily.

You want to combine first and last names into a full name.

You need to add a greeting before a user's name.

You want to merge city and country into one location string.

You want to create a full address from separate parts.

Syntax

PostgreSQL

string1 || string2 || ... || stringN

The || operator joins text values without adding spaces automatically.

Use single quotes for text literals, e.g., 'Hello' || ' ' || 'World'.

Examples

Joins three strings with a space in the middle to form 'Hello World'.

PostgreSQL

'Hello' || ' ' || 'World'

Combines first_name and last_name columns with a space between.

PostgreSQL

first_name || ' ' || last_name

Creates a descriptive location string from city and country columns.

PostgreSQL

'City: ' || city || ', Country: ' || country

Sample Program

This query concatenates 'John' and 'Doe' with a space to show a full name.

PostgreSQL

SELECT 'John' || ' ' || 'Doe' AS full_name;

Important Notes

If any part is NULL, the whole result becomes NULL. Use COALESCE to avoid this.

The || operator works only with text types; cast other types if needed.

Summary

|| operator joins text pieces into one string.

It does not add spaces automatically; add them manually if needed.

Be careful with NULL values as they make the result NULL.

Practice

1. What does the || operator do in PostgreSQL?

easy

A. Joins two or more text strings into one string

B. Adds two numbers together

C. Compares two strings for equality

D. Deletes a row from a table

Solution

1. Step 1: Understand the operator purpose

   The || operator is used to combine text strings in PostgreSQL.

2. Step 2: Differentiate from other operators

   It does not perform arithmetic or comparison; it concatenates strings.

3. Final Answer:

   Joins two or more text strings into one string -> Option A

4. Quick Check:

   Concatenation = Joins strings [OK]

Hint: Remember: || joins text, not numbers or comparisons [OK]

Common Mistakes:

• Thinking || adds numbers
• Confusing || with equality operator
• Assuming it deletes data

2. Which of the following is the correct syntax to concatenate 'Hello' and 'World' with a space in between using || in PostgreSQL?

easy

A. 'Hello' & ' ' & 'World'

B. 'Hello' + ' ' + 'World'

C. 'Hello' CONCAT ' ' CONCAT 'World'

D. 'Hello' || ' ' || 'World'

Solution

1. Step 1: Identify correct concatenation syntax

   PostgreSQL uses || to join strings, so 'Hello' || ' ' || 'World' is correct.

2. Step 2: Check other options for errors

   Options A and B use invalid operators for strings (& and +); D uses incorrect CONCAT syntax.

3. Final Answer:

   'Hello' || ' ' || 'World' -> Option D

4. Quick Check:

   Use || for string join [OK]

Hint: Use || to join strings, not + or & [OK]

Common Mistakes:

• Using + instead of || for strings
• Using & which is not valid in SQL
• Trying CONCAT keyword without function syntax

3. Given the table users with columns first_name = 'Jane' and last_name = 'Doe', what is the result of this query?

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

medium

A. Jane || ' ' || Doe

B. JaneDoe

C. Jane Doe

D. NULL

Solution

1. Step 1: Understand concatenation with spaces

   The query joins first_name, a space, and last_name using ||, so the output includes a space between names.

2. Step 2: Apply values from the table

   With 'Jane' and 'Doe', the result is 'Jane Doe'.

3. Final Answer:

   Jane Doe -> Option C

4. Quick Check:

   Concatenate with space = 'Jane Doe' [OK]

Hint: Add spaces manually when joining strings [OK]

Common Mistakes:

• Forgetting to add space between names
• Expecting || to add space automatically
• Confusing output with literal || symbols

4. What is wrong with this query?

SELECT first_name || last_name FROM users WHERE id = 1;

Assuming first_name is 'John' and last_name is NULL for user with id 1.

medium

A. The query returns NULL

B. The query returns 'JohnNULL'

C. The query causes a syntax error

D. The query returns 'John '

Solution

1. Step 1: Understand NULL behavior in concatenation

   In PostgreSQL, concatenating any string with NULL using || results in NULL.

2. Step 2: Apply to given data

   Since last_name is NULL, first_name || last_name returns NULL, not 'JohnNULL'.

3. Final Answer:

   NULL -> Option A

4. Quick Check:

   NULL in concat makes result NULL [OK]

Hint: NULL in concatenation yields NULL result [OK]

Common Mistakes:

• Expecting NULL to convert to 'NULL' string
• Thinking query has syntax error
• Assuming concatenation adds spaces automatically

5. You want to create a full address string by concatenating street, city, and zipcode columns with commas and spaces in between. Which query correctly handles NULL values so the result never becomes NULL?

SELECT street || ', ' || city || ', ' || zipcode AS full_address FROM addresses;

hard

A. Use CONCAT_WS(',', street, city, zipcode) instead of ||

B. Use COALESCE to replace NULLs: COALESCE(street, '') || ', ' || COALESCE(city, '') || ', ' || COALESCE(zipcode, '')

C. Use + operator instead of || to avoid NULL

D. Leave as is; NULLs are ignored in concatenation

Solution

1. Step 1: Identify NULL handling in concatenation

   Using || with NULL returns NULL, so we must replace NULLs with empty strings using COALESCE.

2. Step 2: Apply COALESCE to each column

   Replace each column with COALESCE(column, '') to avoid NULL results.

3. Final Answer:

   Use COALESCE to replace NULLs: COALESCE(street, '') || ', ' || COALESCE(city, '') || ', ' || COALESCE(zipcode, '') -> Option B

4. Quick Check:

   Replace NULLs with COALESCE to avoid NULL output [OK]

Hint: Use COALESCE to avoid NULL in concatenation [OK]

Common Mistakes:

• Assuming NULLs are ignored automatically
• Using + operator which is invalid for strings
• Not handling NULLs causing full result to be NULL