CASE in PostgreSQL

CJ Chang 0
Viewed 37

I have a SELECT statement in T-SQL which replace null string to "N/A"

select 
  firstName,
  lastName,
  case city
    when null then 'N/A'
    else city
  end,
  case state
    when null then 'N/A'
    else state
  end 
from 
  Person left join Address on Person.personId = Address.personId

Output in T-SQL:

 ------------------------------ ------------------------------ ------------------------------ ------------------------------
 Allen                          Wang                           N/A                            N/A
 Bob                            Alice                          New York City                  New York

How do I do this in PostgreSQL?

sql postgresql
1 Answers

The problem is that you're comparing to NULL, and standard SQL semantics of comparisons of NULL don't work like this. A simple case uses = (equals) behaviour, and something = NULL is UNKNOWN, which inside a CASE behaves as FALSE.

Solutions:

  1. Use coalesce:

    coalesce(state, 'N/A'),

  2. Use a searched case instead of a simple case:

    case when state is null then 'N/A' else state end

dbfiddle: https://dbfiddle.uk/GHWG38c5

Mark Rotteveel0
Related