Concatenation with || operator in PostgreSQL - Mini Project: Build & Apply

Concatenate Customer Names Using || Operator

📖 Scenario: You work at a small online store. The store keeps customer first and last names in separate columns in a table called customers. You want to create a new column that shows the full name by joining the first and last names.

🎯 Goal: Build a SQL query that selects the customer ID and a new column called full_name which combines first_name and last_name using the || concatenation operator with a space between the names.

📋 What You'll Learn

Create a table called customers with columns customer_id, first_name, and last_name.

Insert three customers with exact names: (1, 'John', 'Doe'), (2, 'Jane', 'Smith'), (3, 'Alice', 'Johnson').

Write a SELECT query that concatenates first_name and last_name with a space using the || operator.

Alias the concatenated column as full_name.

💡 Why This Matters

🌍 Real World

Combining first and last names into a full name is common in customer databases, contact lists, and user profiles.

💼 Career

Knowing how to concatenate strings in SQL helps you prepare data for reports, user interfaces, and data exports.

1

Create the customers table and insert data

Create a table called customers with columns customer_id (integer), first_name (text), and last_name (text). Then insert these exact rows: (1, 'John', 'Doe'), (2, 'Jane', 'Smith'), and (3, 'Alice', 'Johnson').

PostgreSQL

-- Create the customers table and insert the three rows
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add the rows.

2

Set up the SELECT statement

Start a SELECT query that selects customer_id from the customers table. Write the FROM clause to specify the table customers.

PostgreSQL

CREATE TABLE customers (
    customer_id INTEGER,
    first_name TEXT,
    last_name TEXT
);

INSERT INTO customers (customer_id, first_name, last_name) VALUES
(1, 'John', 'Doe'),
(2, 'Jane', 'Smith'),
(3, 'Alice', 'Johnson');

-- Start the SELECT query with customer_id and FROM customers
-- Your code here

Hint

Use SELECT customer_id and FROM customers to start your query.

3

Concatenate first_name and last_name with || operator

In the SELECT statement, add a new column that concatenates first_name, a space ' ', and last_name using the || operator. Alias this concatenated column as full_name.

PostgreSQL

CREATE TABLE customers (
    customer_id INTEGER,
    first_name TEXT,
    last_name TEXT
);

INSERT INTO customers (customer_id, first_name, last_name) VALUES
(1, 'John', 'Doe'),
(2, 'Jane', 'Smith'),
(3, 'Alice', 'Johnson');

SELECT customer_id, 
-- Add concatenation of first_name, space, and last_name as full_name
-- Your code here
FROM customers;

Hint

Use first_name || ' ' || last_name AS full_name to join the names with a space.

4

Complete the query and verify structure

Ensure the full query selects customer_id and the concatenated full_name from the customers table. The query should be complete and ready to run.

PostgreSQL

CREATE TABLE customers (
    customer_id INTEGER,
    first_name TEXT,
    last_name TEXT
);

INSERT INTO customers (customer_id, first_name, last_name) VALUES
(1, 'John', 'Doe'),
(2, 'Jane', 'Smith'),
(3, 'Alice', 'Johnson');

SELECT customer_id, 
       first_name || ' ' || last_name AS full_name
FROM customers;

-- Finalize the query structure
-- Your code here

Hint

Make sure the query is complete and selects both columns correctly.

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