Problem with || and data types

From: "Peter Darley" <pdarley(at)kinesis-cem(dot)com>
To: "Pgsql-General" <pgsql-general(at)postgresql(dot)org>
Subject: Problem with || and data types
Date: 2003-04-30 19:50:41
Message-ID: NNEAICKPNOGDBHNCEDCPKEHMDMAA.pdarley@kinesis-cem.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dennis Gearon 2003-04-30 20:04:31 Re: fixed size columns
Previous Message elein 2003-04-30 19:28:27 Re: fixed size columns