Skip site navigation (1) Skip section navigation (2)

Re: Type mismatch problem

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Michael Rowan <mike(dot)rowan(at)internode(dot)on(dot)net>
Cc: pgsql-novice novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Type mismatch problem
Date: 2012-02-06 16:31:34
Message-ID: 19840.1328545894@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
Michael Rowan <mike(dot)rowan(at)internode(dot)on(dot)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

In response to

Responses

pgsql-novice by date

Next:From: Michael RowanDate: 2012-02-06 23:13:48
Subject: Re: Type mismatch problem
Previous:From: Philip CoulingDate: 2012-02-06 10:22:03
Subject: Re: Type mismatch problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group