PostgreSQL CASE expression


how to write a case expression with Postgres? I have this

  when rc.stat = 0 Then unpaid
  when rc.stat = 1 Then paid
  when rc.stat = 2 Then dispute
  else rc.stat

but when I go to run the statement I get an error of

Error: column "unpaid" does not exist

I have also tried double quotes and brackets around the text values, but all of those options give me errors. Just to note, the values unpaid, paid, dispute are NOT fields in the database, but text values I want to write.

Best Answer

You need a string constant there, e.g. 'unpaid'.

Because all branches of a CASE expression must return the same data type, you will also need to cast the stat column to text as well (assuming it's some kind of number as you compare it to one)

  when rc.stat = 0 Then 'unpaid'
  when rc.stat = 1 Then 'paid'
  when rc.stat = 2 Then 'dispute'
  else rc.stat::text

As you only use = as the comparison operator, you can simplify that expression to:

case rc.stat
  when 0 Then 'unpaid'
  when 1 Then 'paid'
  when 2 Then 'dispute'
  else rc.stat::text