Re: Unable to identify an operator '=' for types 'numeric' and 'double precision'

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1



====
CREATE TABLE testdecimal ( decimal_value DECIMAL(6,2));
INSERT INTO testdecimal VALUES(1.0);
INSERT INTO testdecimal VALUES(1.1);
SELECT * FROM testdecimal WHERE decimal_value = 1.0;

Error : Unable to identify an operator '=' for types 'numeric' and
        'double precision'
        You will have to retype this query using and explicit cast
====

In Postgres, the data type "decimal" is a synonym for "numeric": even
though you created the column of type "decimal", Postgres prefers to
use the word "numeric." You may want to use "numeric" in the future
instead of "decimal": while not as intuitive, it aligns the error
messages with your column types.

>Error : Unable to identify an operator '=' for types 'numeric' and
>        'double precision'

The error means that postgres does not know how to compare the values
in your column (which are type 'numeric') with the value you supplied (1.0),
which it considers to be 'double precision.' In this case, you must help
postgres out a little bit by telling it to consider 1.0 as a numeric type.
One way to do this is with the CAST statement, like this:

SELECT * FROM testdecimal WHERE decimal_value = CAST(1.0 as numeric);

Another way to do the same thing with less typing is:

SELECT * FROM testdecimal WHERE decimal_value = 1.0::numeric;

There are other ways to do it as well, but the above two are the best
and most portable ways.


Some related links:


PostgreSQL datatypes: (especially the "numeric" section)

http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/datatype.html


Quick overview of type casting: (mind the line wrapping)
http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS


PostgreSQL type conversion background: (gory details as to how and why)

http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/typeconv.html



Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200203110959

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE8jMaqvJuQZxSWSsgRAoRlAJwOguO9gOsf5/ozujWMN3pf1BCMkQCghs7L
OTzPY4V3rhBtS4CA1tegQqQ=
=o9ST
-----END PGP SIGNATURE-----