Thread: 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!
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)
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!
"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
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!