Обсуждение: Type mismatch problem

Поиск
Список
Период
Сортировка

Type mismatch problem

От
Michael Rowan
Дата:
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

Re: Type mismatch problem

От
Philip Couling
Дата:
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



Re: Type mismatch problem

От
Tom Lane
Дата:
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

Re: Type mismatch problem

От
Michael Rowan
Дата:
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.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 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