Thread: BUG #13934: wrong result of split_part with char value
The following bug has been logged on the website: Bug reference: 13934 Logged by: Dominik Kosiorek Email address: dominik.kosiorek@infobright.com PostgreSQL version: 9.2.2 Operating system: Ubuntu 14 Description: create table string1postgres( a1 int, a2 char(65), a3pattern char(15), a4field int ) insert into string1postgres values(72,'abcd defg poir abcde m ert g d c v b gthb',' ',1); select split_part(a2,a3pattern,a4field) from string1postgres; ------------------- the result of split part is: ------------------- abcd defg poir abcde m ert g d c v b gthb ------------------- instead of: ------------------- abcd ------------------- This defect is only with char type. On varchar result is correct.
On Monday, February 8, 2016, <dominik.kosiorek@infobright.com> wrote: > The following bug has been logged on the website: > > Bug reference: 13934 > Logged by: Dominik Kosiorek > Email address: dominik.kosiorek@infobright.com <javascript:;> > PostgreSQL version: 9.2.2 > Operating system: Ubuntu 14 > Description: > > create table string1postgres( > a1 int, > a2 char(65), > a3pattern char(15), > a4field int > ) > insert into string1postgres values(72,'abcd defg poir abcde m ert g d c v= b > gthb',' ',1); > > select split_part(a2,a3pattern,a4field) from string1postgres; > > ------------------- > the result of split part is: > ------------------- > abcd defg poir abcde m ert g d c v b gthb > > ------------------- > instead of: > ------------------- > abcd > > ------------------- > This defect is only with char type. On varchar result is correct. > > This calls for another round of "don't use char=E2=80=9D advice... It is not a bug but a result of the fact that trailing white space in char values is able to be trimmed away thus leaving you the empty string and no splitting. You may read the documentation for details: http://www.postgresql.org/docs/9.5/static/datatype-character.html And search the Internet for numerous postings as to why you should avoid char. Use to text or varchar instead. David J.
On 02/08/2016 06:00 PM, David G. Johnston wrote: > On Monday, February 8, 2016, <dominik.kosiorek@infobright.com > This calls for another round of "don't use char=E2=80=9D advice... >=20 > It is not a bug but a result of the fact that trailing white space in > char values is able to be trimmed away thus leaving you the empty strin= g > and no splitting. In other words, when you insert ' ' into string1postgres.a3pattern, which is defined as char(15), the single space is trimmed leaving an empty string: test=3D# select '***' || a3pattern || '!!!' from string1postgres; ?column? ---------- ***!!! (1 row) And if you feed an actual space to split_part(), it works as expected. test=3D# select split_part(a2,' ',a4field) from string1postgres; split_part ------------ abcd (1 row) HTH, Joe --=20 Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development
Joe Conway <mail@joeconway.com> writes:
> In other words, when you insert ' ' into string1postgres.a3pattern,
> which is defined as char(15), the single space is trimmed leaving an
> empty string:
> test=# select '***' || a3pattern || '!!!' from string1postgres;
> ?column?
> ----------
> ***!!!
> (1 row)
Actually, I believe the space-trimming happens when the char(n) value
is coerced to type text in preparation for passing it to the || operator
(which takes text). Since trailing spaces are considered insignificant
in char(n), whereas they definitely are significant in text, this is a
reasonable thing to do, at least in some contexts.
regards, tom lane
On Tue, Feb 9, 2016 at 8:55 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Joe Conway <mail@joeconway.com> writes: > > In other words, when you insert ' ' into string1postgres.a3pattern, > > which is defined as char(15), the single space is trimmed leaving an > > empty string: > > > test=3D# select '***' || a3pattern || '!!!' from string1postgres; > > ?column? > > ---------- > > ***!!! > > (1 row) > > Actually, I believe the space-trimming happens when the char(n) value > is coerced to type text in preparation for passing it to the || operator > (which takes text). Since trailing spaces are considered insignificant > in char(n), whereas they definitely are significant in text, this is a > reasonable thing to do, at least in some contexts. > > =E2=80=8BAnd in the OP: =E2=80=8B select split_part(a2,a3pattern,a4field) from string1postgres; =E2=80=8Bsplit_part likewise takes text, not char, and so an implicit conve= rsion and trimming takes place. David j. =E2=80=8B