Michael Rowan <mike.rowan@internode.on.net> writes:
> As a total beginner who has for decades used an application that would allow type mismatches like boolean*numeric I
triedthe 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.
FWIW, what I see is something like
regression=# select 92::numeric(9,2) * (2=1);
ERROR: operator does not exist: numeric * boolean
LINE 1: select 92::numeric(9,2) * (2=1);
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
so I think your terminal must be truncating the message at 79 or 80
columms, which would be a good thing to fix. There are lots of cases
where Postgres error messages will run longer than that.
As far as solving the real problem goes, although Postgres won't let a
boolean be silently treated as a number, you can (in reasonably modern
versions) cast it to integer explicitly:
regression=# select 92::numeric(9,2) * (2=1)::integer;
?column?
----------
0.00
(1 row)
The other respondent's suggestion to use a CASE is probably better
style, but if you just want the minimum change in your habits, this
might help.
regards, tom lane