pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedConcatenation Snafu
5+ messages / 3 participants
[nested] [flat]
* Concatenation Snafu
@ 2003-03-25 22:28 Thomas Good <tomg@sqlclinic.net>
0 siblings, 1 reply; 5+ messages in thread
From: Thomas Good @ 2003-03-25 22:28 UTC (permalink / raw)
To: pgsql-sql
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!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Concatenation Snafu
@ 2003-03-25 23:01 Chad Thompson <chad@weblinkservices.com>
parent: Thomas Good <tomg@sqlclinic.net>
0 siblings, 2 replies; 5+ messages in thread
From: Chad Thompson @ 2003-03-25 23:01 UTC (permalink / raw)
To: Thomas Good <tomg@sqlclinic.net>; pgsql-sql
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)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Concatenation Snafu
@ 2003-03-25 23:30 Thomas Good <tomg@sqlclinic.net>
parent: Chad Thompson <chad@weblinkservices.com>
1 sibling, 0 replies; 5+ messages in thread
From: Thomas Good @ 2003-03-25 23:30 UTC (permalink / raw)
To: Chad Thompson <chad@weblinkservices.com>; +Cc: pgsql-sql
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!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Concatenation Snafu
@ 2003-03-25 23:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Chad Thompson <chad@weblinkservices.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2003-03-25 23:34 UTC (permalink / raw)
To: Chad Thompson <chad@weblinkservices.com>; +Cc: Thomas Good <tomg@sqlclinic.net>; pgsql-sql
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Concatenation Snafu
@ 2003-03-25 23:47 Thomas Good <tomg@sqlclinic.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Thomas Good @ 2003-03-25 23:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Chad Thompson <chad@weblinkservices.com>; pgsql-sql
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!
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-03-25 23:47 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-25 22:28 Concatenation Snafu Thomas Good <tomg@sqlclinic.net>
2003-03-25 23:01 ` Chad Thompson <chad@weblinkservices.com>
2003-03-25 23:30 ` Thomas Good <tomg@sqlclinic.net>
2003-03-25 23:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-25 23:47 ` Thomas Good <tomg@sqlclinic.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox