On Tue, 4 Nov 2014 18:43:38 +0000
Brent Wood <Brent.Wood@niwa.co.nz> wrote:
>
> Looking at the behaviour of char & varchar types, there seems to be an issue. Can anyone explain this behaviour? Is
therea 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;
valuesof 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()'sless 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
trailingspaces, 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