Skip site navigation (1) Skip section navigation (2)

Re: problem porting MySQL SQL to Postgres

From: Paul Thomas <paul(at)tmsl(dot)demon(dot)co(dot)uk>
To: Dan Field <dof(at)llgc(dot)org(dot)uk>
Cc: "pgsql-sql (at) postgresql (dot) org" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: problem porting MySQL SQL to Postgres
Date: 2004-04-15 11:15:23
Message-ID: 20040415121523.A864@bacon (view raw or flat)
Thread:
Lists: pgsql-sql
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   |
+------------------------------+---------------------------------------------+

In response to

Responses

pgsql-sql by date

Next:From: CoLDate: 2004-04-15 11:30:12
Subject: Re: problem porting MySQL SQL to Postgres
Previous:From: Dan FieldDate: 2004-04-15 10:25:59
Subject: problem porting MySQL SQL to Postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group