PostgreSQL CASE expression

Question

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

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

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.

Answer 1

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)

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

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
end

• Timmy Turner: Ohhh, so the issue was I was trying to write text values to an int field - but casting the field to a text field resolves :)
• user1822: @TimmyTurner: your original issue was that you didn't write your string constants with single quotes.