Обсуждение: Re: Unable to identify an operator '=' for types 'numeric' and 'double precision'
Re: Unable to identify an operator '=' for types 'numeric' and 'double precision'
От
"Greg Sabino Mullane"
Дата:
-----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-----