Thanks Tom
Understood re error messages.
Tried (partial select):
SELECT sum(CASE WHEN type=1 THEN cost*soldqty ELSE 0 END) AS type1sales::numeric(9,2), etc etc
which caused an "ERROR syntax error at or near ::"
whereas:
SELECT sum(CASE WHEN type=1 THEN cost*soldqty ELSE 0 END) AS type1sales, etc etc
works.
I can round the resulting data later, but it would be nice to do it with the elegance PostgreSQL provides if one knows how.
Mike
On 07/02/2012, at 3:01 AM, Tom Lane wrote:
92::
Michael Rowan
mike.rowan@internode.on.net11 Kingscote Street
ALBERTON
South Australia 5014
tel 618 8240 3993
On 07/02/2012, at 3:01 AM, Tom Lane wrote:
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 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.
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
Michael Rowan
11 Kingscote Street
ALBERTON
South Australia 5014
tel 618 8240 3993