From: | Michael Rowan <mike(dot)rowan(at)internode(dot)on(dot)net> |
---|---|
To: | pgsql-novice novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Type mismatch problem |
Date: | 2012-02-06 23:13:48 |
Message-ID: | 024DE498-531D-4731-9C0B-905837942589@internode.on.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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(dot)rowan(at)internode(dot)on(dot)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(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
Michael Rowan
mike(dot)rowan(at)internode(dot)on(dot)net
11 Kingscote Street
ALBERTON
South Australia 5014
tel 618 8240 3993
From | Date | Subject | |
---|---|---|---|
Next Message | Alessandro Gagliardi | 2012-02-07 18:30:13 | timestamp with time zone |
Previous Message | Tom Lane | 2012-02-06 16:31:34 | Re: Type mismatch problem |