How to do text calculations using columns in SQL

January 24, 2022

I’d like to conditionally include the values from particular columns in a SQL query. To illustrate the question let me use the fictional example of returning selective data about users.

If we were to return all data we’d use a query of the form:

SELECT 
  name, email, gender 
FROM 
  users

Assume all users have entered their gender. However some have set the value of gender_public to be false and I would like to reflect this in the data the query returns.

The type of thing I’d like to do is:

SELECT 
  name, email, if(gender_public, gender, 'N/A')
FROM 
  users

Is this possible? I’m working with Postgres

>Solution :

Using CASE

This is the obvious way to go, as suggested by @a_horse_with_no_name.

Assuming gender_public is of type BOOLEAN.

SELECT name, email, CASE WHEN gender_public THEN gender ELSE 'N/A' END AS gender
FROM users;

Using lateral join

This way of doing is especially useful if you need to use the value in more than one place and want to remain consistent.

SELECT name, email, T.gender
FROM users
CROSS JOIN LATERAL (
  SELECT gender WHERE show_gender
  UNION
  SELECT 'N/A' WHERE NOT(show_gender)
) T(gender)