Обсуждение: Type mismatch problem
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. Any clues would be most welcome. Michael Rowan mike.rowan@internode.on.net 11 Kingscote Street ALBERTON South Australia 5014 tel 618 8240 3993
On 06/02/2012 09:52, Michael Rowan wrote:
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
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
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
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:
Michael Rowan
mike.rowan@internode.on.net
11 Kingscote Street
ALBERTON
South Australia 5014
tel 618 8240 3993
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.net
11 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 etcIn 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