Re: Type mismatch problem

On 06/02/2012 09:52, Michael Rowan wrote:

As a total beginner who has for decades used an application that would allow type mismatches like boolean*numeric I tried the following select:

SELECT sum(cost*quantity)*(sales_type=1) AS sales_type1, sum(cost*quantity)*(sales_type=2) AS sales_type2 FROM etc etc

In the above, cost and quantity are TYPE numeric(9,2), sales_type is smallint.

PostgreSQL does not allow numeric*boolean.  The error message ends with "You might need t"  which kinda leaves me hanging.

Any clues would be most welcome.


Michael Rowan
mike.rowan@internode.on.net

11 Kingscote Street
ALBERTON
South Australia 5014

tel 618 8240 3993

In PostgreSQL boolean are not numeric as you have found.  you can use a CASE statement to get the desired result.
SELECT
CASE
    WHEN sales_type = 1 THEN sum(cost*quantity)
    ELSE 0
END as sales_type2
FROM etc etc

Its a little verbose but it works.

Of course you can use the entire case statement as a numeric also as long as all return types are numeric:
SELECT
CASE
    WHEN sales_type = 1 THEN 1
    ELSE 0
END * sum(cost*quantity) as sales_type2
FROM etc etc

Hope this helps