PostgreSQL: How to Use Concat in a WHERE Clause

Often in PostgreSQL you may want to concatenate strings inside of a WHERE clause.

You can use the following syntax to do so:

SELECT *
FROM athletes
WHERE (first_name || ' ' || last_name) = 'Adam Smith';

This particular example will select all rows from the athletes table where the concatenated result of the strings in the first_name and last_name column is Adam Smith.

The following example shows how to use this syntax in practice.

Example: How to Use Concat in WHERE Clause in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  id INTEGER PRIMARY KEY,
  first_name TEXT,
  last_name TEXT
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Adam', 'Smith' );
INSERT INTO athletes VALUES (0002, 'Adam', 'Anderson');
INSERT INTO athletes VALUES (0003, 'Bob', 'Smith');
INSERT INTO athletes VALUES (0004, 'Bob', 'Jenkins');
INSERT INTO athletes VALUES (0005, 'Chad', 'Arnold');
INSERT INTO athletes VALUES (0006, 'Adam', 'Smith');
INSERT INTO athletes VALUES (0007, 'Mike', 'Smith');
INSERT INTO athletes VALUES (0008, 'Greg', 'Wintz');

-- view all rows in table
SELECT * FROM athletes;

 id | first_name | last_name 
----+------------+-----------
  1 | Adam       | Smith
  2 | Adam       | Anderson
  3 | Bob        | Smith
  4 | Bob        | Jenkins
  5 | Chad       | Arnold
  6 | Adam       | Smith
  7 | Mike       | Smith
  8 | Greg       | Wintz

Suppose that we would like to select all rows where the concatenated result of the strings in the first_name and last_name column is Adam Smith.

We can use the following syntax to do so:

SELECT *
FROM athletes
WHERE (first_name || ' ' || last_name) = 'Adam Smith';

 id | first_name | last_name 
----+------------+-----------
  1 | Adam       | Smith
  6 | Adam       | Smith

Notice that this returns the two rows from the table where the concatenated result of the strings in the first_name and last_name column is Adam Smith.

Feel free to use similar syntax to concatenate strings in a WHERE clause in your own query in PostgreSQL.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Concatenate JSON Arrays
PostgreSQL: How to Concatenate Strings Using GROUP BY
PostgreSQL: How to Remove Specific Characters from String