pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: problem porting MySQL SQL to Postgres
8+ messages / 7 participants
[nested] [flat]
* Re: problem porting MySQL SQL to Postgres
@ 2004-04-15 09:55 Reinoud van Leeuwen <reinoud.v@n.leeuwen.net>
parent: Dan Field <dof@llgc.org.uk>
3 siblings, 0 replies; 8+ messages in thread
From: Reinoud van Leeuwen @ 2004-04-15 09:55 UTC (permalink / raw)
To: pgsql-sql
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
__________________________________________________
^ permalink raw reply [nested|flat] 8+ messages in thread
* problem porting MySQL SQL to Postgres
@ 2004-04-15 10:25 Dan Field <dof@llgc.org.uk>
0 siblings, 4 replies; 8+ messages in thread
From: Dan Field @ 2004-04-15 10:25 UTC (permalink / raw)
To: pgsql-sql
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
TIA
--
Dan Field
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: problem porting MySQL SQL to Postgres
@ 2004-04-15 11:15 Paul Thomas <paul@tmsl.demon.co.uk>
parent: Dan Field <dof@llgc.org.uk>
3 siblings, 1 reply; 8+ messages in thread
From: Paul Thomas @ 2004-04-15 11:15 UTC (permalink / raw)
To: Dan Field <dof@llgc.org.uk>; +Cc: 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 |
+------------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: problem porting MySQL SQL to Postgres
@ 2004-04-15 11:30 CoL <col@mportal.hu>
parent: Dan Field <dof@llgc.org.uk>
3 siblings, 0 replies; 8+ messages in thread
From: CoL @ 2004-04-15 11:30 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: problem porting MySQL SQL to Postgres
@ 2004-04-15 11:30 Martin Marques <martin@bugs.unl.edu.ar>
parent: Dan Field <dof@llgc.org.uk>
3 siblings, 0 replies; 8+ messages in thread
From: Martin Marques @ 2004-04-15 11:30 UTC (permalink / raw)
To: Dan Field <dof@llgc.org.uk>; pgsql-sql
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
-----------------------------------------------------------------
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: problem porting MySQL SQL to Postgres
@ 2004-04-18 18:37 Markus Bertheau <twanger@bluetwanger.de>
parent: Paul Thomas <paul@tmsl.demon.co.uk>
0 siblings, 1 reply; 8+ messages in thread
From: Markus Bertheau @ 2004-04-18 18:37 UTC (permalink / raw)
To: Paul Thomas <paul@tmsl.demon.co.uk>; +Cc: Dan Field <dof@llgc.org.uk>; pgsql-sql
В Чтв, 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>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: problem porting MySQL SQL to Postgres
@ 2004-04-18 19:24 Paul Thomas <paul@tmsl.demon.co.uk>
parent: Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 1 reply; 8+ messages in thread
From: Paul Thomas @ 2004-04-18 19:24 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: Dan Field <dof@llgc.org.uk>; pgsql-sql
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 |
+------------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: problem porting MySQL SQL to Postgres
@ 2004-04-19 03:21 Bruno Wolff III <bruno@wolff.to>
parent: Paul Thomas <paul@tmsl.demon.co.uk>
0 siblings, 0 replies; 8+ messages in thread
From: Bruno Wolff III @ 2004-04-19 03:21 UTC (permalink / raw)
To: Paul Thomas <paul@tmsl.demon.co.uk>; +Cc: Markus Bertheau <twanger@bluetwanger.de>; Dan Field <dof@llgc.org.uk>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2004-04-19 03:21 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-15 10:25 problem porting MySQL SQL to Postgres Dan Field <dof@llgc.org.uk>
2004-04-15 09:55 ` Reinoud van Leeuwen <reinoud.v@n.leeuwen.net>
2004-04-15 11:15 ` Paul Thomas <paul@tmsl.demon.co.uk>
2004-04-18 18:37 ` Markus Bertheau <twanger@bluetwanger.de>
2004-04-18 19:24 ` Paul Thomas <paul@tmsl.demon.co.uk>
2004-04-19 03:21 ` Bruno Wolff III <bruno@wolff.to>
2004-04-15 11:30 ` CoL <col@mportal.hu>
2004-04-15 11:30 ` Martin Marques <martin@bugs.unl.edu.ar>
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