Examples with character types - Amazon Redshift

Examples with character types

CREATE TABLE statement

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.