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.
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