Postgres Pro
Downloads
Home   >   mailing lists

Thread: Problem with || and data types

Problem with || and data types

From
"Peter Darley"
Date:
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

Re: Problem with || and data types

From
"Peter Darley"
Date:
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@postgresql.org
[mailto:pgsql-general-owner@postgresql.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@postgresql.org)

Re: Problem with || and data types

From
Tom Lane
Date:
"Peter Darley" <pdarley@kinesis-cem.com> writes:
>     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')".

Doesn't look that way from here:

regression=# explain
regression-# select f1 from int4_tbl where f1 = (select unique1 from tenk1) || 'test';
                            QUERY PLAN
-------------------------------------------------------------------
 Seq Scan on int4_tbl  (cost=0.00..1.10 rows=1 width=4)
   Filter: ((f1)::text = (($0)::text || 'test'::text))
   InitPlan
     ->  Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=4)
(4 rows)

Whether this is a particularly sensible interpretation I dunno, but
for sure it's binding || more tightly than =.

There are related syntaxes (= ANY and so forth) that are treated like
generic operators and so would bind left-to-right in this example:

regression=# explain
regression-# select f1 from int4_tbl where f1 = any (select unique1 from tenk1) || 'test';
ERROR:  Unable to identify an operator '||' for types 'boolean' and '"unknown"'
        You will have to retype this query using an explicit cast

But AFAICT 7.2 does that the same way.

            regards, tom lane

Re: Problem with || and data types

From
Tom Lane
Date:
"Peter Darley" <pdarley@kinesis-cem.com> writes:
> ... 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

Actually, the first operator you have there is "<>" not "=".  "<>" is
considered a generic Op, just like "||", so the binding will be left-to-
right.  AFAIK this was the same in 7.2 and for a good ways before.

In short, you need some parentheses.

            regards, tom lane

Re: Problem with || and data types

From
"Peter Darley"
Date:
Tom and Bijoy,
    The parentheses was the answer.
    I was under the impression that this was working in 7.2.1 because I had
forgotten that I was making some refinements to my application as I was
testing it with the new version of PostgreSQL.  You are right, of course, it
doesn't work in 7.2.1 without the parentheses.
    So, everyone, let me be a lesson to you.  Only change one thing at a time
or else you'll get confused and look stupid.
Thanks,
Peter Darley

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, April 30, 2003 6:34 PM
To: Peter Darley
Cc: Pgsql-General
Subject: Re: [GENERAL] Problem with || and data types


"Peter Darley" <pdarley@kinesis-cem.com> writes:
> ... 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

Actually, the first operator you have there is "<>" not "=".  "<>" is
considered a generic Op, just like "||", so the binding will be left-to-
right.  AFAIK this was the same in 7.2 and for a good ways before.

In short, you need some parentheses.

            regards, tom lane