From: | "Peter Darley" <pdarley(at)kinesis-cem(dot)com> |
---|---|
To: | "Peter Darley" <pdarley(at)kinesis-cem(dot)com>, "Pgsql-General" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Problem with || and data types |
Date: | 2003-04-30 23:41:45 |
Message-ID: | NNEAICKPNOGDBHNCEDCPEEIADMAA.pdarley@kinesis-cem.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Folks,
I've figured out the problem here... When I have a where clause that
contains "x=(select x from y where z) || 'test'" it's interpreting this as
"(x=(select x from y where z)) || 'test'" instead of "x=((select x from y
where z) || 'test')".
This looks like a change from 7.2.1 to 7.3.2. Shouldn't the = operator
have pretty much the lowest precedence of any operator?
Thanks,
Peter Darley
-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Peter Darley
Sent: Wednesday, April 30, 2003 12:51 PM
To: Pgsql-General
Subject: [GENERAL] Problem with || and data types
Friends,
I've got a very bizarre and frustrating problem with concatenation and data
types. I have a query that doesn't have a single piece of Boolean data in
it, but gives me an error that mentions a bool:
UPDATE Import_Items SET Name=(SELECT QT.Import_As FROM Question_Types QT,
Border_Type_Translation BTT WHERE QT.Value=BTT.First_Type AND
BTT.Second_Type=Border_Questions.Type) || ':' || Border_Questions.Field_Name
FROM Border_Questions WHERE 'Quantum_' || Border_Questions.Question_ID =
Import_Items.Foreign_Key AND Import_Items.Name <>(SELECT QT.Import_As FROM
Question_Types QT, Border_Type_Translation BTT WHERE QT.Value=BTT.First_Type
AND BTT.Second_Type=Border_Questions.Type) || ':' ||
Border_Questions.Field_Name
ERROR: Unable to identify an operator '||' for types 'boolean' and
'"unknown"'
You will have to retype this query using an explicit cast
The problem is in the final part of the where that reads:
AND BTT.Second_Type=Border_Questions.Type) || ':' ||
Border_Questions.Field_Name
If I remove it the query runs correctly.
I can get rid of the 'unkknown' bit by casting the ':' to varchar, but then
I get:
ERROR: Unable to identify an operator '||' for types 'boolean' and
'character varying'
You will have to retype this query using an explicit cast
If I explicitly cast each variable in the phrase as a varchar, and even add
a paranthetical cast of concatinated parts to ensure that their product is a
varchar, I still get a complaint about concatinating bools:
UPDATE Import_Items SET Name=(SELECT QT.Import_As FROM Question_Types QT,
Border_Type_Translation BTT WHERE QT.Value=BTT.First_Type AND
BTT.Second_Type=Border_Questions.Type) || ':' || Border_Questions.Field_Name
FROM Border_Questions WHERE 'Quantum_' || Border_Questions.Question_ID =
Import_Items.Foreign_Key AND Import_Items.Name::varchar <>(SELECT
QT.Import_As::varchar FROM Question_Types QT, Border_Type_Translation BTT
WHERE QT.Value=BTT.First_Type AND
BTT.Second_Type=Border_Questions.Type)::varchar || (':'::varchar ||
Border_Questions.Field_Name::varchar)::varchar
ERROR: Unable to identify an operator '||' for types 'boolean' and
'character varying'
You will have to retype this query using an explicit cast
I get the same thing if I cast every variable in the entire query as a
varchar. Any one have any idea what's going on here or how I can get it to
work?
Thanks,
Peter Darley
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-05-01 00:03:44 | Re: dump/restore to 7.4devel giving "[archiver (db)] error returned by PQputline" |
Previous Message | Dennis Gearon | 2003-04-30 22:59:39 | Re: why don't string function work in WHERE clause? |