Thread: problem porting MySQL SQL to Postgres
I've stumbled across a query I don't quite understand the error message for. This query is pulled from a working MySQL setup: SELECTDEWEY_ID, DEWEY_HUNDREDS, DEWEY_TENS, DEWEY_ONES, DEWEY_POINT_ONES, DEWEY_POINT_TENS, DEWEY_POINT_HUNDREDS, DEWEY_POINT_THOUSANDS, DEWEY_TYPE, DEWEY_LANG, DEWEY_SUBJECT FROM lu_dewey WHERE(DEWEY_HUNDREDS = 9) AND(DEWEY_TENS >= 0) AND (DEWEY_TENS <= 9) AND(DEWEY_ONES = 0 || DEWEY_ONES = NULL) AND(DEWEY_POINT_ONES= 0 || DEWEY_POINT_ONES = NULL) AND(DEWEY_POINT_TENS = 0 || DEWEY_POINT_TENS = NULL) AND(DEWEY_POINT_HUNDREDS= 0 || DEWEY_POINT_HUNDREDS = NULL) AND(DEWEY_POINT_THOUSANDS = 0 || DEWEY_POINT_THOUSANDS = NULL)AND(DEWEY_TYPE = 't') AND(DEWEY_LANG = 'en') ORDER BY DEWEY_TENS However I'm getting the following error: ERROR: Unable to identify an operator '=' for types 'character' and 'boolean' You will have to retype this query using an explicit cast. Any help would be much appreciated TIA -- Dan Field
On Thu, Apr 15, 2004 at 11:25:59AM +0100, Dan Field wrote: > I've stumbled across a query I don't quite understand the error message > for. > > This query is pulled from a working MySQL setup: > > SELECT > DEWEY_ID, DEWEY_HUNDREDS, DEWEY_TENS, DEWEY_ONES, DEWEY_POINT_ONES, > DEWEY_POINT_TENS, > DEWEY_POINT_HUNDREDS, DEWEY_POINT_THOUSANDS, DEWEY_TYPE, > DEWEY_LANG, DEWEY_SUBJECT FROM lu_dewey > WHERE > (DEWEY_HUNDREDS = 9) AND > (DEWEY_TENS >= 0) AND > (DEWEY_TENS <= 9) AND > (DEWEY_ONES = 0 || DEWEY_ONES = NULL) AND > (DEWEY_POINT_ONES = 0 || DEWEY_POINT_ONES = NULL) AND > (DEWEY_POINT_TENS = 0 || DEWEY_POINT_TENS = NULL) AND > (DEWEY_POINT_HUNDREDS = 0 || DEWEY_POINT_HUNDREDS = NULL) AND > (DEWEY_POINT_THOUSANDS = 0 || DEWEY_POINT_THOUSANDS = NULL) AND please use 'IS NULL' instead of '= NULL' > (DEWEY_TYPE = 't') AND > (DEWEY_LANG = 'en') > ORDER BY DEWEY_TENS > > > However I'm getting the following error: > > ERROR: Unable to identify an operator '=' for types 'character' and > 'boolean' You will have to retype this query using an explicit cast. can you post the output of '\d lu_dewey'? -- __________________________________________________ "Nothing is as subjective as reality" Reinoud van Leeuwen reinoud.v@n.leeuwen.net http://www.xs4all.nl/~reinoud __________________________________________________
On 15/04/2004 11:25 Dan Field wrote: > I've stumbled across a query I don't quite understand the error message > for. > > This query is pulled from a working MySQL setup: > > SELECT > DEWEY_ID, DEWEY_HUNDREDS, DEWEY_TENS, DEWEY_ONES, > DEWEY_POINT_ONES, DEWEY_POINT_TENS, DEWEY_POINT_HUNDREDS, > DEWEY_POINT_THOUSANDS, DEWEY_TYPE, DEWEY_LANG, DEWEY_SUBJECT > FROM lu_dewey > WHERE > (DEWEY_HUNDREDS = 9) AND > (DEWEY_TENS >= 0) AND (DEWEY_TENS <= 9) AND > (DEWEY_ONES = 0 || DEWEY_ONES = NULL) AND > (DEWEY_POINT_ONES = 0 || DEWEY_POINT_ONES = NULL) AND > (DEWEY_POINT_TENS = 0 || DEWEY_POINT_TENS = NULL) AND > (DEWEY_POINT_HUNDREDS = 0 || DEWEY_POINT_HUNDREDS = NULL) AND > (DEWEY_POINT_THOUSANDS = 0 || DEWEY_POINT_THOUSANDS = NULL) AND > (DEWEY_TYPE = 't') AND > (DEWEY_LANG = 'en') > ORDER BY DEWEY_TENS > > > However I'm getting the following error: > > ERROR: Unable to identify an operator '=' for types 'character' and > 'boolean' You will have to retype this query using an explicit cast. > > > Any help would be much appreciated You're trying to use the string concatenation operator (||) in a boolean test.. That's an invalid comparison according to the SQL specs. You need to use the SQL OR operator e.e., (DEWEY_POINT_TENS = 0 OR DEWEY_POINT_TENS = NULL) AND ~~ Your "= NULL" tests are also not valid SQL (should be IS NULL). MySQL does not follow the specs in a number or areas. PostgreSQL is very standards-complient. Write valid SQL and you should be OK. HTH -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+
El Jue 15 Abr 2004 07:25, Dan Field escribió: > I've stumbled across a query I don't quite understand the error message > for. > > This query is pulled from a working MySQL setup: > > SELECT > DEWEY_ID, DEWEY_HUNDREDS, DEWEY_TENS, DEWEY_ONES, DEWEY_POINT_ONES, > DEWEY_POINT_TENS, > DEWEY_POINT_HUNDREDS, DEWEY_POINT_THOUSANDS, DEWEY_TYPE, DEWEY_LANG, > DEWEY_SUBJECT FROM lu_dewey > WHERE > (DEWEY_HUNDREDS = 9) AND > (DEWEY_TENS >= 0) AND > (DEWEY_TENS <= 9) AND > (DEWEY_ONES = 0 || DEWEY_ONES = NULL) AND > (DEWEY_POINT_ONES = 0 || DEWEY_POINT_ONES = NULL) AND > (DEWEY_POINT_TENS = 0 || DEWEY_POINT_TENS = NULL) AND > (DEWEY_POINT_HUNDREDS = 0 || DEWEY_POINT_HUNDREDS = NULL) AND > (DEWEY_POINT_THOUSANDS = 0 || DEWEY_POINT_THOUSANDS = NULL) AND > (DEWEY_TYPE = 't') AND > (DEWEY_LANG = 'en') > ORDER BY DEWEY_TENS > > > However I'm getting the following error: > > ERROR: Unable to identify an operator '=' for types 'character' and > 'boolean' You will have to retype this query using an explicit cast. 1) Change the "= NULL" to "IS NULL" which is how it's defined in the SQL standards. 2) What data type does DEWEY_TYPE have? -- 08:28:01 up 37 days, 12:55, 2 users, load average: 0.72, 0.77, 0.90 ----------------------------------------------------------------- Martín Marqués | select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral -----------------------------------------------------------------
В Чтв, 15.04.2004, в 13:15, Paul Thomas пишет: > On 15/04/2004 11:25 Dan Field wrote: > Your "= NULL" tests are also not valid SQL (should be IS NULL). I think = NULL _is_ valid SQL, it just doesn't do what you think. -- Markus Bertheau <twanger@bluetwanger.de>
On 18/04/2004 19:37 Markus Bertheau wrote: > РЧÑв, 15.04.2004, в 13:15, Paul Thomas пиÑеÑ: > > On 15/04/2004 11:25 Dan Field wrote: > > > Your "= NULL" tests are also not valid SQL (should be IS NULL). > > I think = NULL _is_ valid SQL, it just doesn't do what you think. It's valid in an assignment update foo set bar = null; PostgreSQL can be coerced into accepting where foo = null with the transform_null_equals run-time option but that doesn't make it legal SQL IFAIK. -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+
On Sun, Apr 18, 2004 at 20:24:47 +0100, Paul Thomas <paul@tmsl.demon.co.uk> wrote: > > PostgreSQL can be coerced into accepting where foo = null with the > transform_null_equals run-time option but that doesn't make it legal SQL > IFAIK. foo = NULL is a valid SQL expression that always returns NULL.
hi, Dan Field wrote: > I've stumbled across a query I don't quite understand the error message > for. > > This query is pulled from a working MySQL setup: > > SELECT > DEWEY_ID, DEWEY_HUNDREDS, DEWEY_TENS, DEWEY_ONES, > DEWEY_POINT_ONES, DEWEY_POINT_TENS, > DEWEY_POINT_HUNDREDS, DEWEY_POINT_THOUSANDS, DEWEY_TYPE, > DEWEY_LANG, DEWEY_SUBJECT FROM lu_dewey > WHERE > (DEWEY_HUNDREDS = 9) AND > (DEWEY_TENS >= 0) AND > (DEWEY_TENS <= 9) AND > (DEWEY_ONES = 0 || DEWEY_ONES = NULL) AND > (DEWEY_POINT_ONES = 0 || DEWEY_POINT_ONES = NULL) AND > (DEWEY_POINT_TENS = 0 || DEWEY_POINT_TENS = NULL) AND > (DEWEY_POINT_HUNDREDS = 0 || DEWEY_POINT_HUNDREDS = NULL) AND > (DEWEY_POINT_THOUSANDS = 0 || DEWEY_POINT_THOUSANDS = NULL) AND > (DEWEY_TYPE = 't') AND > (DEWEY_LANG = 'en') > ORDER BY DEWEY_TENS > > > However I'm getting the following error: > > ERROR: Unable to identify an operator '=' for types 'character' and > 'boolean' You will have to retype this query using an explicit cast. use OR not ||. C.