sql - PostgreSQL - return string when testing integer with case statement -


when selecting data table i'd use case statement return "n/a" in event of null value.

this simplified representative version of table i'm looking at:

create table test (     id integer not null,     paid_cents integer null default null,     primary key (id) );  insert test values (1, 100); insert test values (2, null); insert test values (3, 0); 

i'd expect able use following query:

select case     when paid_cents null 'n/a'     else paid_cents / 100     end "dollar amount" test 

a fiddle of behaves i'd expect. trying on real database (postgresql 9.4) results in error:

error: invalid input syntax integer: "n/a"
line 2: when paid_cents null 'n/a'

it seems postgres expecting integer returned when testing integer (and changing 'n/a' number indeed work). i'm guessing i'll need cast somewhere?

either use null:

select case     when paid_cents null null     else paid_cents / 100     end "dollar amount" test 

or cast both string:

select case     when paid_cents null 'n/a'     else (paid_cents / 100)::text     end "dollar amount" test 

Comments

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

python Tkinter Capturing keyboard events save as one single string -

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -