CODEWITHSUNDEEP
sqldatabasepostgresqlcasting
dev1234
dev1234

Reputation: 5716

CAST in PostgreSQL

I have this query and i need to set 'yes' and 'no' when t.delete_req='1' and t.delete_req='0'

SELECT t.id,t.activity_id,t.type,t.sub_type,t.name,t.description,t.source, 
CASE t.delete_req WHEN t.delete_req='1' THEN 'Yes' WHEN t.delete_req='0' THEN 'No' END from "activities" "t"

it throws and error and says some casting problem is there. Below is the error.

ERROR:  operator does not exist: character = boolean
LINE 2: CASE t.delete_req WHEN t.delete_req='1' THEN 'Yes' WHEN t.de...
                          ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

now i tried casting it this way, // CAST(t.delete_req AS char)='1'

SELECT t.id,t.activity_id,t.type,t.sub_type,t.name,t.description,t.source, 
CASE t.delete_req 
WHEN CAST(t.delete_req AS char)='1' THEN 'Yes' 
WHEN CAST(t.delete_req AS char)='0' THEN 'No' END from "activities" "t"

but still i couldnt make it work. error is same. 

ERROR:  operator does not exist: character = boolean

in table, t.delete_req is a char and defined as below.

delete_req character(1) NOT NULL DEFAULT '0'::bpchar,

Upvotes: 3

Views: 10417

Answers (1)

roman
roman

Reputation: 117380

you have to change syntax a little bit (formatted for clarity):

select
    t.id,t.activity_id,t.type,t.sub_type,t.name,t.description,t.source, 
    case t.delete_req
        when '1' then 'Yes'
        when '0' then 'No'
    end
from activities as t

or

select
    t.id,t.activity_id,t.type,t.sub_type,t.name,t.description,t.source, 
    case
        when t.delete_req = '1' then 'Yes'
        when t.delete_req = '0' then 'No'
    end
from activities as t

Upvotes: 1

Related Questions