Postgres data types integer and boolean

Question

I have a sql table (version 9.3 PostgreSQL) with fields (tour character varying, tourtime int)... I want to write a query like case when tour = 'A' then tourtime = 2 when tour = 'B' then tourtime = 3 else 0 etc..

However i get the error

CASE types integer and boolean cannot be matched

Why is this?

Cast boolean as integer? tourtime::int4

sibert
– sibert

2016-01-05 06:34:39 +00:00
Commented Jan 5, 2016 at 6:34 — sibert
– sibert, Commented Jan 5, 2016 at 6:34

Renzo · Accepted Answer · 2016-01-05 10:34:19Z

4

In your CASE you have tree branches:

 case when tour = 'A' 
        then tourtime = 2 
      when tour = 'B' 
        then tourtime = 3 
      else 0
 end

In the first two, you have boolean expressions ((tourtime = 2) and (tourtime = 3)), which are true or false according to the value of the column tourtime. In the third one, you have an integer expression (0).

When the system tries to infer the type of the result it matches the three types and found that they are different.

So you should have either all integer expressions, or boolean expressions, depending on what you want as result.

edited Jan 5, 2016 at 10:34

answered Jan 5, 2016 at 7:05

Renzo

27.5k5 gold badges54 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Elad Over a year ago

You are missing END in your case.

Collectives™ on Stack Overflow

Postgres data types integer and boolean

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related