Postgres char type inconsistency

Поиск
Список
Период
Сортировка
От Brent Wood
Тема Postgres char type inconsistency
Дата
Msg-id 5895e5a1c32344f08325516457f1a510@welwex02.niwa.local
обсуждение исходный текст
Ответ на Re: Tablespace limit feature  (Stephen Frost <sfrost@snowman.net>)
Ответы Re: Postgres char type inconsistency  (Bill Moran <wmoran@potentialtech.com>)
Re: Postgres char type inconsistency  (Adrian Klaver <adrian.klaver@aklaver.com>)
Список pgsql-general


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..

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
NIWA



Вложения

В списке pgsql-general по дате отправления:

Предыдущее
От: Kirit Parmar
Дата:
Сообщение: Re: DATA corruption after promoting slave to master
Следующее
От: Raymond O'Donnell
Дата:
Сообщение: Re: phppgadmin