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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
