Select with IF statement on postgresql

Question

I have a code like that:

select
    tbl.person
    ,COUNT(distinct tbl.project)
    ,if (tbl.stage like '%SIGNED%') then sum(tbl.value) else '0' end if as test
from
    my_table tbl
group by
1

And it returns me that error message:

SQL Error [42601]: ERROR: syntax error at or near "then"

I didn't got it. As I saw on documentation, the if statement syntax appears to be used correctly

Answer 1

IF is to be used in procedures, not in queries. Use a case expression instead:

select
    tbl.person
    ,COUNT(distinct tbl.project)
    ,sum(case when tbl.stage like '%SIGNED%' then tbl.value else 0 end) as test
from
    my_table tbl
group by
1

Notes:

• tbl.stage is not part of the group by, so it should most probably be enclosed within the aggregate expression, not outside of it

• all values returned by a case expression need to have the same datatype. Since sum(tbl.value) is numeric, the else part of the case should return 0 (number), not '0' (string).