I’m having problems with the case statement in PostgreSQL. Here I want to change the output of the month (number) to text, like January, February, etc.
select
date_part('month', tanggal) bulan,
case
when date_part('month', tanggal)= 1 then 'January'
when date_part('month', tanggal) = 2 then 'February'
when date_part('month', tanggal) = 3 then 'March'
when date_part('month', tanggal) = 4 then 'April'
when date_part('month', tanggal) = 5 then 'May'
when date_part('month', tanggal) = 6 then 'June'
when date_part('month', tanggal) = 7 then 'July'
when date_part('month', tanggal) = 8 then 'August'
when date_part('month', tanggal) = 9 then 'September'
when date_part('month', tanggal) = 10 then 'October'
when date_part('month', tanggal) = 11 then 'November'
when date_part('month', tanggal) = 12 then 'December'
else date_part('month', tanggal)
end as namabulan
But, what I got was an error like this
ERROR: invalid input syntax for type double precision: "Januari"
LINE 4: when date_part('month', tanggal)= 1 then 'Januari'
^
I was trying with a changed data type in the case statement ‘January’, but I got an error with the statement not matching with double precision.
2
Answers
The literals all have type ‘unknown’, leaving the ELSE to determine the return type of the CASE. But then the literals can’t be interpreted as being of that type.
This will return the name of the month without the CASE statement:
see: DBFIDDLE, which also shows how to fix the problem in your CASE statement.
and documentation of postgresql: full mixed-case month name (blank-padded to 9 chars)
NOTE: When you do not want 9 characters, you can always use rtrim()