pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
operator does not exist 4+ messages / 3 participants [nested] [flat]
* operator does not exist @ 2010-03-19 07:24 Julius Tuskenis <julius@nsoft.lt> 0 siblings, 2 replies; 4+ messages in thread From: Julius Tuskenis @ 2010-03-19 07:24 UTC (permalink / raw) To: pgsql-admin Hello, we updated one of our systems from postgresql 8.2 to 8.3 and now some queries are generating errors that no operator matches the given name and argument type. That is not surprising as in v8.3 the automatic casting was removed what troubles me is that sometimes it still casts select '1' = 1 results in true while select sum(msg_price) from messages where msg_itemid = 0 results in error: ERROR: operator does not exist: character varying = integer LINE 1: select sum(msg_price) from messages where msg_itemid = 0 I will add the operator ant then we'll fix the queries, but is the first example ok? Should it not raise error ? -- Julius Tuskenis Programavimo skyriaus vadovas UAB nSoft mob. +37068233050 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: operator does not exist @ 2010-03-19 09:16 Dimitri Fontaine <dfontaine@hi-media.com> parent: Julius Tuskenis <julius@nsoft.lt> 1 sibling, 1 reply; 4+ messages in thread From: Dimitri Fontaine @ 2010-03-19 09:16 UTC (permalink / raw) To: Julius Tuskenis <julius@nsoft.lt>; +Cc: pgsql-admin Julius Tuskenis <julius@nsoft.lt> writes: > select '1' = 1 results in true This is a undecorated literal, which PostgreSQL will cast as integer when it discovers that's what makes sense. > while select sum(msg_price) from messages where msg_itemid = 0 results in > error: > ERROR: operator does not exist: character varying = integer > LINE 1: select sum(msg_price) from messages where msg_itemid = 0 Here, msg_itemid is known to be a varchar, and 0 is a numeric literal, which is in the range of an integer, so the type is resolved as an integer. Now the = operator does not exist for varchar, integer. > I will add the operator ant then we'll fix the queries, but is the first > example ok? Should it not raise error ? Not as written. Try to decorate the literal to force PostgreSQL into considering it of the type you have in mind: SELECT text '1' = 1; Or even do a cast, this way: SELECT '1'::text = 1; Regards, -- dim ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: operator does not exist @ 2010-03-19 10:16 Julius Tuskenis <julius@nsoft.lt> parent: Dimitri Fontaine <dfontaine@hi-media.com> 0 siblings, 0 replies; 4+ messages in thread From: Julius Tuskenis @ 2010-03-19 10:16 UTC (permalink / raw) To: pgsql-admin Thank you for your informative answer. -- Julius Tuskenis Programavimo skyriaus vadovas UAB nSoft mob. +37068233050 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: operator does not exist @ 2010-03-19 12:33 Tom Lane <tgl@sss.pgh.pa.us> parent: Julius Tuskenis <julius@nsoft.lt> 1 sibling, 0 replies; 4+ messages in thread From: Tom Lane @ 2010-03-19 12:33 UTC (permalink / raw) To: Julius Tuskenis <julius@nsoft.lt>; +Cc: pgsql-admin Julius Tuskenis <julius@nsoft.lt> writes: > we updated one of our systems from postgresql 8.2 to 8.3 and now some > queries are generating errors that no operator matches the given name > and argument type. That is not surprising as in v8.3 the automatic > casting was removed what troubles me is that sometimes it still casts What was removed was a small number of cases where it would implicitly cast non-string datatypes to text; which, as often as not, was wrong and resulted in surprising behavior. It is not correct to say that we got rid of implicit casts altogether. For example, in the case you give, > ... where msg_itemid = 0 ... > ERROR: operator does not exist: character varying = integer msg_itemid could have a value like '00'. It's not immediately obvious to the reader whether this should be considered equal to the integer constant 0. 8.3 and up now require you to either quote the constant (so that it can be considered a string rather than an integer), in which case you'd get textual comparison and '00' is different from '0'; or explicitly cast msg_itemid to integer, in which case you'd get numeric comparison and '00'::integer will be equal to 0. What you were getting before was silent use of textual comparison, which might or might not be what you really wanted. > select '1' = 1 results in true That's a completely different behavior: the quoted constant, which is initially considered of "unknown" type, gets resolved as integer because an integer is what it's being compared to. It's worth comparing this to what will happen if you fix your query as I suggest: ... where msg_itemid = '0' ... What really happens under the hood is that '0' is initially considered of unknown type, and then after the parser observes that it's being compared to a varchar variable, the constant's type is resolved as varchar, allowing the '=' operator to be resolved as varchar = varchar. This behavior is the same in all existing releases of Postgres. regards, tom lane ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-03-19 12:33 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2010-03-19 07:24 operator does not exist Julius Tuskenis <julius@nsoft.lt> 2010-03-19 09:16 ` Dimitri Fontaine <dfontaine@hi-media.com> 2010-03-19 10:16 ` Julius Tuskenis <julius@nsoft.lt> 2010-03-19 12:33 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox