Thread: Concatenation Snafu

Concatenation Snafu

From
Thomas Good
Date:
SELECT code || ' ' || diag, code
FROM dsm4
WHERE axis = 1
ORDER BY code;

This worked on 6.3-7.3.1 now it dies with:
'unable to identify an operator || for types 'character' and 'character
varying'

What happened?????

-----------------------------------------------------------------------
Thomas Good                                  e-mail: tomg@sqlclinic.net
Programmer/Analyst                           phone:   (+1) 718.818.5528
Residential Services                         fax:     (+1) 718.818.5056
Behavioral Health Services, SVCMC-NY         mobile:  (+1) 917.282.7359

// Krieg ist selbst Terror - F�r Frieden und globale Gerechtigkeit!



Re: Concatenation Snafu

From
"Chad Thompson"
Date:
The assumtion that char and varchar can be compared is gone.  Any comparison
or in this case concatination between the two types needs to be explicitly
cast.

try
SELECT code::varchar || ' ' || diag::varchar, code
FROM dsm4
WHERE axis = 1
ORDER BY code;

Thanks
Chad
----- Original Message -----
From: "Thomas Good" <tomg@sqlclinic.net>
To: "Postgres SQL List" <pgsql-sql@postgreSQL.org>
Sent: Tuesday, March 25, 2003 3:28 PM
Subject: [SQL] Concatenation Snafu



SELECT code || ' ' || diag, code
FROM dsm4
WHERE axis = 1
ORDER BY code;

This worked on 6.3-7.3.1 now it dies with:
'unable to identify an operator || for types 'character' and 'character
varying'

What happened?????

-----------------------------------------------------------------------
Thomas Good                                  e-mail: tomg@sqlclinic.net
Programmer/Analyst                           phone:   (+1) 718.818.5528
Residential Services                         fax:     (+1) 718.818.5056
Behavioral Health Services, SVCMC-NY         mobile:  (+1) 917.282.7359

// Krieg ist selbst Terror - Für Frieden und globale Gerechtigkeit!


---------------------------(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: Concatenation Snafu

From
Thomas Good
Date:
On Tue, 25 Mar 2003, Chad Thompson wrote:

> The assumtion that char and varchar can be compared is gone.  Any comparison
> or in this case concatination between the two types needs to be explicitly
> cast.
>
> try
> SELECT code::varchar || ' ' || diag::varchar, code
> FROM dsm4
> WHERE axis = 1
> ORDER BY code;

Hi Chad, yes I did a CAST(code AS varchar) and it works...I suppose this
is an "improvement" to Pg as it prevents the risk of standardising our
concatenation syntax with Oracle.  ;-)

This sort of thing is irritating when you have to support more than
one type of backend!

Thanks for the post,
Tom
-----------------------------------------------------------------------
Thomas Good                                  e-mail: tomg@sqlclinic.net
Programmer/Analyst                           phone:   (+1) 718.818.5528
Residential Services                         fax:     (+1) 718.818.5056
Behavioral Health Services, SVCMC-NY         mobile:  (+1) 917.282.7359

// Krieg ist selbst Terror - F�r Frieden und globale Gerechtigkeit!



Re: Concatenation Snafu

From
Tom Lane
Date:
"Chad Thompson" <chad@weblinkservices.com> writes:
> The assumtion that char and varchar can be compared is gone.

The real problem is that we have too dang many || operators; there's
no reason to have char||char, varchar||varchar, *and* text||text.
The parser can't decide which of the first two to prefer, so it
punts.  If we got rid of both and left only text||text, all would be
well.

Thomas is wrong to claim that this used to behave differently; all
versions I have handy to test, back to 7.0, give the same error.
        regards, tom lane



Re: Concatenation Snafu

From
Thomas Good
Date:
On Tue, 25 Mar 2003, Tom Lane wrote:

> "Chad Thompson" <chad@weblinkservices.com> writes:
> > The assumtion that char and varchar can be compared is gone.
>
> The real problem is that we have too dang many || operators; there's
> no reason to have char||char, varchar||varchar, *and* text||text.
> The parser can't decide which of the first two to prefer, so it
> punts.  If we got rid of both and left only text||text, all would be
> well.

I'd prefer we matched Oracle syntax whenever possible.  Then again I
don't like typing all that much.  ;-)

> Thomas is wrong to claim that this used to behave differently; all
> versions I have handy to test, back to 7.0, give the same error.

You are correct.  I checked it again: I must have tripped over this
before as the datatypes on one system (7.3.1) don't match the other
(7.3.2) in essentially the same table.


-----------------------------------------------------------------------
Thomas Good                                  e-mail: tomg@sqlclinic.net
Programmer/Analyst                           phone:   (+1) 718.818.5528
Residential Services                         fax:     (+1) 718.818.5056
Behavioral Health Services, SVCMC-NY         mobile:  (+1) 917.282.7359

// Krieg ist selbst Terror - F�r Frieden und globale Gerechtigkeit!