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.