Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

Examples with Character Types


The following CREATE TABLE statement demonstrates the use of VARCHAR and CHAR data types:

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) );
The following examples use this table.

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 ');
select count(*) from address where address1='9516 Magnolia Boulevard'; count ------- 2 (1 row)
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 return 2.

Results of the LENGTH Function

The LENGTH function recognizes trailing blanks in VARCHAR columns:

select length(address1) from address; length -------- 23 25 (2 rows)
A value of Augusta in 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:

insert into address(city_name) values('City of South San Francisco'); ERROR: value too long for type character(20)
A workaround for this problem is to cast the value to the size of the column:

insert into address(city_name) values('City of South San Francisco'::char(20));
In this case, the first 20 characters of the string (City of South San Fr) would be loaded into the column.