Thread: no "+" operator for smallint and bigint
Postgres (7.3.2) doesn't seem to know how to add smallint's to bigint's ...
PG=# select 8::smallint + 8::bigint;
ERROR: Unable to identify an operator '+' for types 'smallint' and 'bigint'
You will have to retype this query using an explicit cast
This is surely just an oversight right?
I was trying to add two columns, a smallint col to a bigint col, and
store the result in the bigint col ... only to find to my surprise that
PostgreSQL didn't know how to add smallints to bigints ...
This will be fixed right? ;)
Jc
On Mon, Feb 24, 2003 at 08:19:20PM +0900, Jean-Christian Imbeault wrote: > Postgres (7.3.2) doesn't seem to know how to add smallint's to bigint's ... > > PG=# select 8::smallint + 8::bigint; > ERROR: Unable to identify an operator '+' for types 'smallint' and 'bigint' > You will have to retype this query using an explicit cast > > This is surely just an oversight right? Actually no. Postgresql's type system is completely generic so it no idea that bigints and smallints are both numbers and that it can make sense to add them. Since there is no explicit operator it doesn't know if the result should be a smallint or a bigint so it's asking you to tell it. IMHO, there is a virtue in not having the database automatically cast columns in an effort to guess what you mean. If it's ambiguous, make it clear. > I was trying to add two columns, a smallint col to a bigint col, and > store the result in the bigint col ... only to find to my surprise that > PostgreSQL didn't know how to add smallints to bigints ... > > This will be fixed right? ;) Well, it not quite a straight forward problem and there has been much discussion about how to solve it. I think 7.3 is better but I'm not sure. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Support bacteria! They're the only culture some people have.
Attachment
Martijn van Oosterhout <kleptog@svana.org> writes:
> On Mon, Feb 24, 2003 at 08:19:20PM +0900, Jean-Christian Imbeault wrote:
>> PG=3D# select 8::smallint + 8::bigint;
>> ERROR: Unable to identify an operator '+' for types 'smallint' and 'bigi=
> nt'
> Since there is no explicit operator it doesn't know if the result should be
> a smallint or a bigint so it's asking you to tell it.
More specifically, the parser finds two equally plausible choices: cast the
smallint to int and apply "int4 + int8", or cast the smallint to bigint
and apply "int8 + int8". Not knowing which to prefer, it has to punt.
> Well, it not quite a straight forward problem and there has been much
> discussion about how to solve it.
That's an understatement :-(.
I currently like the idea of getting rid of as many cross-datatype
operators as possible --- offering a native "int4 + int8" operator
doesn't seem to have any obvious benefit over letting the parser
insert a promotion. But there are some pitfalls in that idea too.
regards, tom lane
Why not do it like C does, promote to the RESULTANT data type. If he had rewritten his statement like so, then it would work as per my statement above: operator# select (8::smallint + 8::bigint)::biginit; 2/24/2003 6:59:44 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >Martijn van Oosterhout <kleptog@svana.org> writes: >> On Mon, Feb 24, 2003 at 08:19:20PM +0900, Jean-Christian Imbeault wrote: >>> PG=3D# >>> ERROR: Unable to identify an operator '+' for types 'smallint' and 'bigi= >> nt' > >> Since there is no explicit operator it doesn't know if the result should be >> a smallint or a bigint so it's asking you to tell it. > >More specifically, the parser finds two equally plausible choices: cast the >smallint to int and apply "int4 + int8", or cast the smallint to bigint >and apply "int8 + int8". Not knowing which to prefer, it has to punt. > >> Well, it not quite a straight forward problem and there has been much >> discussion about how to solve it. > >That's an understatement :-(. > >I currently like the idea of getting rid of as many cross-datatype >operators as possible --- offering a native "int4 + int8" operator >doesn't seem to have any obvious benefit over letting the parser >insert a promotion. But there are some pitfalls in that idea too. > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster >