Problem with || and data types - Mailing list pgsql-general
From | Peter Darley |
---|---|
Subject | Problem with || and data types |
Date | |
Msg-id | NNEAICKPNOGDBHNCEDCPKEHMDMAA.pdarley@kinesis-cem.com Whole thread Raw |
Responses |
Re: Problem with || and data types
("Peter Darley" <pdarley@kinesis-cem.com>)
Re: Problem with || and data types (Tom Lane <tgl@sss.pgh.pa.us>) |
List | 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
pgsql-general by date: