Concatenation with || operator in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Concatenate two columns with || operator

Given a table users with columns first_name and last_name, what is the output of this query?

SELECT first_name || ' ' || last_name AS full_name FROM users WHERE id = 1;

PostgreSQL

CREATE TABLE users (id INT, first_name TEXT, last_name TEXT);
INSERT INTO users VALUES (1, 'Alice', 'Smith');

A [{"full_name": "Alice Smith"}]

B [{"full_name": "AliceSmith"}]

C [{"full_name": "Alice || Smith"}]

D SyntaxError

💡 Hint

The || operator joins strings exactly as written, including spaces.

The || operator concatenates strings. Here, it joins first_name, a space, and last_name to form 'Alice Smith'.

📝 Syntax

intermediate

Identify the syntax error in concatenation

Which option will cause a syntax error when trying to concatenate two strings in PostgreSQL?

A SELECT 'Hello' + ' World';

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

C SELECT 'Hello' || ' World';

D SELECT 'Hello' || 'World';

💡 Hint

PostgreSQL does not use + for string concatenation.

In PostgreSQL, the + operator is not valid for strings and causes a syntax error. The || operator is used instead.

❓ query_result

advanced

Concatenate with NULL values

What is the output of this query if middle_name is NULL?

SELECT first_name || ' ' || middle_name || ' ' || last_name AS full_name FROM users WHERE id = 2;

PostgreSQL

CREATE TABLE users (id INT, first_name TEXT, middle_name TEXT, last_name TEXT);
INSERT INTO users VALUES (2, 'John', NULL, 'Doe');

A [{"full_name": "John Doe"}]

B [{"full_name": "John Doe"}]

C [{"full_name": "JohnNULL Doe"}]

D [{"full_name": null}]

💡 Hint

In PostgreSQL, concatenating with NULL results in NULL.

The || operator returns NULL if any operand is NULL. So the entire concatenation is NULL.

❓ query_result

advanced

Concatenate with COALESCE to handle NULL

How to modify the query to show full name even if middle_name is NULL?

SELECT first_name || ' ' || COALESCE(middle_name, '') || ' ' || last_name AS full_name FROM users WHERE id = 2;

PostgreSQL

CREATE TABLE users (id INT, first_name TEXT, middle_name TEXT, last_name TEXT);
INSERT INTO users VALUES (2, 'John', NULL, 'Doe');

A [{"full_name": "John Doe"}]

B [{"full_name": "John Doe"}]

C [{"full_name": "JohnNULL Doe"}]

D [{"full_name": null}]

💡 Hint

COALESCE replaces NULL with the given value.

COALESCE(middle_name, '') replaces NULL with empty string, so concatenation works and shows two spaces between first and last name.

🧠 Conceptual

expert

Why does concatenation with || return NULL if any operand is NULL?

In PostgreSQL, why does the || operator return NULL if any string operand is NULL?

A Because || operator converts NULL to 'NULL' string before concatenation.

B Because || operator treats NULL as empty string automatically.

C Because NULL represents unknown, so concatenation with unknown yields unknown (NULL).

D Because || operator ignores NULL operands and concatenates the rest.

💡 Hint

Think about what NULL means in databases.

NULL means unknown or missing value. Concatenating with unknown results in unknown, so the result is 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