Often in PostgreSQL you may want to check if a row already exists in a table with a specific value in a particular column.
You can use the EXISTS statement with the following syntax to do so:
SELECT EXISTS(SELECT 1 FROM athletes WHERE id=8) AS id_exists;
This particular example checks if a row exists in the athletes table with a value of 8 in the id column.
This query returns either true or false as a result.
The following example shows how to use this syntax in practice.
Example: How to Check if Row Exists in Table 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,
team TEXT,
position TEXT,
points INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES (1, 'Mavs', 'Guard', 15);
INSERT INTO athletes VALUES (2, 'Mavs', 'Guard', 12);
INSERT INTO athletes VALUES (3, 'Mavs', 'Forward', 23);
INSERT INTO athletes VALUES (4, 'Mavs', 'Center', 40);
INSERT INTO athletes VALUES (5, 'Hawks', 'Guard', 29);
INSERT INTO athletes VALUES (6, 'Hawks', 'Forward', 14);
INSERT INTO athletes VALUES (7, 'Hawks', 'Forward', 33);
INSERT INTO athletes VALUES (8, 'Hawks', 'Center', 12);
-- view all rows in table
SELECT * FROM athletes;
id | team | position | points
----+-------+----------+--------
1 | Mavs | Guard | 15
2 | Mavs | Guard | 12
3 | Mavs | Forward | 23
4 | Mavs | Center | 40
5 | Hawks | Guard | 29
6 | Hawks | Forward | 14
7 | Hawks | Forward | 33
8 | Hawks | Center | 12
Suppose that we would like to check if a row exists with a value of 8 in the id column of the table.
We can use the following syntax to do so:
SELECT EXISTS(SELECT 1 FROM athletes WHERE id=8) AS id_exists;
id_exists
-----------
t
The output displays t (true) since there is indeed a row with a value of 8 in the id column of the table.
Suppose instead that we check if a row exists with a value of 9 in the id column:
SELECT EXISTS(SELECT 1 FROM athletes WHERE id=9) AS id_exists;
id_exists
-----------
f
The output displays f (false) since there is not a row with a value of 9 in the id column of the table.
You can use similar syntax to check if a row exists in your own table in PostgreSQL.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Rename Column Only if Exists
PostgreSQL: How to Add Column if Not Exists
PostgreSQL: How to INSERT If Not Exists Already
PostgreSQL: How to Find Duplicate Records in a Table
