Examples with Character Types
CREATE TABLE Statement
The following CREATE TABLE statement demonstrates the use of VARCHAR and CHAR data types:
The following examples use this table.Copy
create table address( address_id integer, address1 varchar(100), address2 varchar(50), district varchar(20), city_name char(20), state char(2), postal_code char(5) );
Trailing Blanks in Variable-Length Character Strings
Because ADDRESS1 is a VARCHAR column, the trailing blanks in the second inserted address are semantically insignificant. In other words, these two inserted addresses match.
insert into address(address1) values('9516 Magnolia Boulevard'); insert into address(address1) values('9516 Magnolia Boulevard ');
If the ADDRESS1 column were a CHAR column and the same values were inserted, the COUNT(*) query would recognize the character strings as the same and returnCopy
select count(*) from address where address1='9516 Magnolia Boulevard'; count ------- 2 (1 row)
Results of the LENGTH Function
The LENGTH function recognizes trailing blanks in VARCHAR columns:
A value ofCopy
select length(address1) from address; length -------- 23 25 (2 rows)
Augustain the CITY_NAME column, which is a CHAR column, would always return a length of 7 characters, regardless of any trailing blanks in the input string.
Values That Exceed the Length of the Column
Character strings are not truncated to fit the declared width of the column:
A workaround for this problem is to cast the value to the size of the column:Copy
insert into address(city_name) values('City of South San Francisco'); ERROR: value too long for type character(20)
In this case, the first 20 characters of the string (Copy
insert into address(city_name) values('City of South San Francisco'::char(20));
City of South San Fr) would be loaded into the column.