On 11/04/2014 10:43 AM, Brent Wood wrote:
>
> Looking at the behaviour of char & varchar types, there seems to be an
> issue. Can anyone explain this behaviour? Is there a bug of some sort?
>
> According to the docs
> http://www.postgresql.org/docs/9.3/static/datatype-character.html)(:
> " If the string to be stored is shorter than the declared length, values
> of type character will be space-padded; values of type character varying
> will simply store the shorter string."
>
> Yet chars are not being padded, in fact they lose trailing spaces which
> are retained by varchars. They also return length()'s less than the
> defined length... which should not be the case for a padded string as
> defined in the documentation.
>
> fish=# create table test(var3 varchar(3),cha3 char(3));
> CREATE TABLE
> fish=# insert into test values('1','1');
> INSERT 0 1
> fish=# insert into test values('2 ','2 '); -- one space
> INSERT 0 1
> fish=# insert into test values('3 ','3 '); --two spaces
> INSERT 0 1
> fish=# select var3||':' as var3, cha3||':' as char3 from test;
> var3 | char3
> ------+-------
> 1: | 1:
> 2 : | 2:
> 3 : | 3:
> (3 rows)
> test=# select length(var3) as v_lgth, length(cha3) as c_length from test;
> v_lgth | c_length
> --------+----------
> 1 | 1
> 2 | 1
> 3 | 1
>
> So, in summary, varchar stores whatever feed to it and keeps trailing
> spaces to max length, char type will trim off trailing spaces, and stor
> a string shorter than the specified length..
I believe what you are seeing is explained here:
http://www.postgresql.org/docs/9.3/interactive/datatype-character.html
"Values of type character are physically padded with spaces to the
specified width n, and are stored and displayed that way. However, the
padding spaces are treated as semantically insignificant. Trailing
spaces are disregarded when comparing two values of type character, and
they will be removed when converting a character value to one of the
other string types. Note that trailing spaces are semantically
significant in character varying and text values, and when using pattern
matching, e.g. LIKE, regular expressions."
>
> Brent Wood
>
> Programme leader: Environmental Information Delivery
> NIWA
> DDI: +64 (4) 3860529
>
> Brent Wood
> Principal Technician - GIS and Spatial Data Management
> Programme Leader - Environmental Information Delivery
>
> +64-4-386-0529| 301 Evans Bay Parade, Greta Point, Wellington|
> www.niwa.co.nz <http://www.niwa.co.nz>
>
> NIWA <http://www.niwa.co.nz>
>
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
