Обсуждение: Change in behavior of string concat operator
I just migrated from 8.1 to 8.3, running on FreeBSD 7.0 amd64.
Running 8.3.5
I have a query concatenating 3 columns to create a hash.
userid is a numeric(38,0) field, accountno is an integer, and requestdate is a date.
Under 8.1, Select userid || accountno || requestdate::date as newcolumn from table executed ok.
Under 8.3, it gives the following error:
operator does not exist: numeric || integer
Typecasting the first two columns to varchar resolved the problem.
Select userid::varchar || accountno::varchar || requestdate::date as newcolumn from table
At the very least, the documentation should be updated to reflect the new behavior, although I believe this is a bug.
Benjamin Krajmalnik написа: > I just migrated from 8.1 to 8.3, running on FreeBSD 7.0 amd64. > Running 8.3.5 [...] > Select userid::varchar || accountno::varchar || requestdate::date as > newcolumn from table > At the very least, the documentation should be updated to reflect the > new behavior, although I believe this is a bug. http://www.postgresql.org/docs/current/static/release-8-3.html#AEN86272 -- Milen A. Radev
Thanks. Then we have an inconsistency between this and the documentation for the || operator, where it is showing the abilty to concatnumeric types without explicit casting. http://www.postgresql.org/docs/8.3/static/functions-string.html > -----Original Message----- > From: pgsql-admin-owner@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Milen A. Radev > Sent: Saturday, December 06, 2008 12:49 PM > To: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Change in behavior of string concat operator > > Benjamin Krajmalnik написа: > > I just migrated from 8.1 to 8.3, running on FreeBSD 7.0 amd64. > > Running 8.3.5 > [...] > > Select userid::varchar || accountno::varchar || > requestdate::date as > > newcolumn from table At the very least, the documentation should be > > updated to reflect the new behavior, although I believe > this is a bug. > > > http://www.postgresql.org/docs/current/static/release-8-3.html > #AEN86272 > > > > -- > Milen A. Radev > > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin >
Benjamin Krajmalnik написа: > Thanks. > > Then we have an inconsistency between this and the documentation for the || operator, where it is showing the abilty toconcat numeric types without explicit casting. > > http://www.postgresql.org/docs/8.3/static/functions-string.html On second reading of your post (and the documentation) we are both wrong - the _string_ concatenation operator works when at least one of the operands is a string. > >> -----Original Message----- >> From: pgsql-admin-owner@postgresql.org >> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Milen A. Radev >> Sent: Saturday, December 06, 2008 12:49 PM >> To: pgsql-admin@postgresql.org >> Subject: Re: [ADMIN] Change in behavior of string concat operator >> >> Benjamin Krajmalnik написа: >>> I just migrated from 8.1 to 8.3, running on FreeBSD 7.0 amd64. >>> Running 8.3.5 >> [...] >>> Select userid::varchar || accountno::varchar || >> requestdate::date as >>> newcolumn from table At the very least, the documentation should be >>> updated to reflect the new behavior, although I believe >> this is a bug. >> >> >> http://www.postgresql.org/docs/current/static/release-8-3.html >> #AEN86272 [...] -- Milen A. Radev
On Mon, 8 Dec 2008, Benjamin Krajmalnik wrote: > Thanks. > > Then we have an inconsistency between this and the documentation for the > || operator, where it is showing the abilty to concat numeric types > without explicit casting. > > http://www.postgresql.org/docs/8.3/static/functions-string.html What that page appears to say in the note and table is that you can concatenate a string and a non-string like varchar and integer, not multiple non-strings like numeric and integer. What specifically were you looking at on the page?
Thanks, Stephan and Milen. Everything is clear now. I had a change in the behavior moving from 8.1 to 8.3, and the automatic typecasting prior to 8.3 blurred the intended behavior as per the documentation.