On Tue, 4 Nov 2014 18:43:38 +0000 Brent Wood <brent.w...@niwa.co.nz> 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.. Your conclusion is wrong. The spaces _are_stored_. You've missed some possibilities. In all of your examples above, the || operator casts the char to varchar before executing. The _cast_ from char to varchar is what trims the spaces. You can see this visually with psql with something like this: db=# select '3'::char(33); bpchar ----------------------------------- 3 (1 row) db=# select '3'::char(33)::varchar; varchar --------- 3 (1 row) I seem to remember discussion about this actually being correct behavior per the SQL standard, but I could be wrong on this count. Quite frankly, I don't see any reason for anyone using char any more. If I had to guess, I would guess that char is in the standard because at the time of creation there were systems that could heavily optimize access to fix-width fields, and that it's still in the standard becuase nobody is sure how to clean cruft out of the standard. -- Bill Moran I need your help to succeed: http://gamesbybill.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
