PostgreSQL: How to Select Columns Based on Condition

Often in PostgreSQL you may want to select column values based on a condition.

You can use the following syntax to do so:

SELECT id,
       points,
       (CASE WHEN all_star = 'Yes' THEN all_star END) AS all_star
FROM athletes;

This particular example will only return the values from the all_star column where the value in the column is equal to ‘Yes’, otherwise no value will be returned.

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

Example: How to Select Columns Based on Condition in PostgreSQL

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

-- create table named 'store1'
CREATE TABLE athletes (
  id INTEGER,
  all_star TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (1, 'Yes', 40);
INSERT INTO athletes VALUES (2, 'No', 16);
INSERT INTO athletes VALUES (3, 'No', 12);
INSERT INTO athletes VALUES (4, 'No', 31);
INSERT INTO athletes VALUES (5, 'Yes', 40);
INSERT INTO athletes VALUES (6, 'Yes', 25);
INSERT INTO athletes VALUES (7, 'No', 12);
INSERT INTO athletes VALUES (8, 'No', 11);

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

 id | all_star | points 
----+----------+--------
  1 | Yes      |     40
  2 | No       |     16
  3 | No       |     12
  4 | No       |     31
  5 | Yes      |     40
  6 | Yes      |     25
  7 | No       |     12
  8 | No       |     11

Suppose that we would like to only select the values from the all_star column where the value in the column is equal to ‘Yes’, otherwise no value will be returned.

We can use the following syntax to do so:

SELECT id,
       points,
       (CASE WHEN all_star = 'Yes' THEN all_star END) AS all_star
FROM athletes;

 id | points | all_star 
----+--------+----------
  1 |     40 | Yes
  2 |     16 | 
  3 |     12 | 
  4 |     31 | 
  5 |     40 | Yes
  6 |     25 | Yes
  7 |     12 | 
  8 |     11 | 

From the output we can see that only the values in the all_star column that are equal to ‘Yes’ have been returned.

You can use similar syntax to select column values based on a condition in your own table in PostgreSQL.

Additional Resources

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

PostgreSQL: How to Count Occurrences of Each Value in Column
PostgreSQL: How to Count Number of Occurrences of Character in String
PostgreSQL: How to Count True and False Values