Shulou(Shulou.com)06/01 Report--

No zuo no die series, from pg wiki.

This section says: Do not use char(n).

The reason is:

Any string you insert into a char(n) field will be padded with spaces to the declared width. That's probably not what you actually want.

The manual says:

Values of type character are physically padded with spaces to the specified width n, and are stored and displayed that way. However, trailing spaces are treated as semantically insignificant and disregarded when comparing two values of type character. In collations where whitespace is significant, this behavior can produce unexpected results; for example SELECT 'a '::CHAR(2) collate "C" < E'a\n'::CHAR(2) returns true, even though C locale would consider a space to be greater than a newline. Trailing spaces are 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, that is LIKE and regular expressions.

That should scare you off it.

The space-padding does waste space, but doesn't make operations on it any faster; in fact the reverse, thanks to the need to strip spaces in many contexts.

It's important to note that from a storage point of view char(n) is not a fixed-width type. The actual number of bytes varies since characters may take more than one byte, and the stored values are therefore treated as variable-length anyway (even though the space padding is included in the storage).

The reason is that it is expected to specify n length, but due to character encoding (such as Chinese characters, GB2312 is 2 bytes, and UTF8 is 3 bytes), the actual situation does not match the expectation, and other side effects such as affecting sorting will occur.

testdb=# drop table if exists t_char;DROP TABLEtestdb=# create table t_char (id int,c1 char(10),c2 varchar(10));CREATE TABLEtestdb=# testdb=# insert into t_char values (1,'test123',' 123123');INSERT 0 1testdb=# insert into t_char values (2,'abc123','123123');INSERT 0 1testdb=# testdb=# insert into t_char values(3,'a','a ');INSERT 0 1testdb=# insert into t_char values(4,E'a\n',E'a\n');INSERT 0 1testdb=#

Use the length function to get the length

testdb=# select id,length(c1),length(c2) from t_char order by id; id | length | length ----+--------+-------- 1 | 5 | 6 2 | 6 | 6 3 | 1 | 2 4 | 2 | 2(4 rows)

As mentioned above, the actual number of characters obtained by using the length function is not the actual number of bytes, for example, the actual number of bytes for "Test 123" is 9+5=14 bytes.

testdb=# select id,length(c1),octet_length(c1),length(c2),octet_length(c2) from t_char order by id; id | length | octet_length | length | octet_length ----+--------+--------------+--------+-------------- 1 | 5 | 14 | 6 | 6 2 | 6 | 10 | 6 | 6 3 | 1 | 10 | 2 | 2 4 | 2 | 10 | 2 | 2(4 rows)

In string comparison above, although the ascii code value of the space (0x20) is larger than '\n'(0x0a), the actual effect of the query seems to be that char(10) defines'a'smaller than'a\n':

testdb=# select E'a\n'::bytea; bytea -------- \x610a(1 row)testdb=# select E'a '::bytea; bytea -------- \x6120(1 row)testdb=# select * from t_char where c1 < E'a\n'; id | c1 | c2 ----+------------+---- 3 | a | a (1 row)

resources

Don't Do This

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.