Concatenation with || operator in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the || operator do in PostgreSQL?

The || operator joins two or more strings together into one combined string.

beginner

Write a simple example using || to join 'Hello' and 'World'.

SELECT 'Hello' || ' ' || 'World'; -- Result: 'Hello World'

intermediate

Can you use || to concatenate NULL with a string? What happens?

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

intermediate

How to safely concatenate a nullable column with a string?

Use COALESCE(column, '') to replace NULL with empty string before concatenation.

beginner

Is the || operator specific to PostgreSQL or standard SQL?

The || operator is part of the SQL standard for string concatenation and supported by PostgreSQL.

What is the result of SELECT 'Hi' || ' ' || 'There';?

A 'Hi There'

B 'HiThere'

C 'Hi || There'

D Error

The || operator joins strings with no added spaces unless you add them explicitly.

What happens if you concatenate 'Hello' || NULL in PostgreSQL?

A NULL

B 'Hello'

C 'HelloNULL'

D Error

Concatenating with NULL results in NULL unless handled with COALESCE.

Which function helps avoid NULL results when concatenating nullable columns?

A LENGTH()

B COALESCE()

C SUBSTRING()

D TRIM()

COALESCE() replaces NULL with a specified value, like an empty string.

Is || operator used for arithmetic addition or string concatenation?

A Bitwise OR

B Arithmetic addition

C Logical OR

D String concatenation

In PostgreSQL, || joins strings together.

Which of these is a valid way to concatenate first_name and last_name with a space?

A CONCAT_WS(' ', first_name, last_name)

B first_name || ' ' || last_name

C Both B and C

D first_name + ' ' + last_name

Both || operator and CONCAT_WS function can concatenate strings with spaces.

Explain how to concatenate two strings in PostgreSQL and handle NULL values safely.

Think about what happens if one string is missing.

Describe the difference between using || operator and CONCAT_WS function for string joining.

Consider how spaces or separators are added.