Trailing Blanks in Character Data
This section explains how CHAR
and VARCHAR
columns handle
trailing blanks (space characters) when character data is loaded, stored, and queried.
CHAR
data type is conceptually a fixed-length, blank-padded string. The
rules for handling trailing blanks in fixed-length character strings are as follows:CHAR
columns are stored with any trailing blanks trimmed. For example, values loaded into aCHAR(10)
column as'London'
and'London '
are stored with an equal length of 6 characters. TheLENGTH
function for both values returns 6.- A comparison of values such as
'London'
and'London '
(one trailing blank) or'London'
and'London '
(two trailing blanks) returns a match. - Trailing blanks in
CHAR
columns are restored on output to the client. For example, aCHAR(10)
column returns 10 characters per column value.
VARCHAR
data type is a variable-length character string. The rules for
processing variable-length strings are as follows:VARCHAR
columns are stored and processed with any trailing blanks preserved. For example, values loaded into aVARCHAR(10)
column as'London'
and'London '
are stored with lengths of 6 and 8 characters, respectively.- A comparison of values such as
'London'
and'London '
does not return a match. VARCHAR
columns are returned to the client as variable-length strings. For example, aVARCHAR(10)
column returns anything from 0 to 10 characters.
season
table called, which has the following
schema:premdb=# \d season
Table "public.season"
Column | Type | Modifiers
-------------+-----------------------+-----------
seasonid | smallint |
season_name | character(12) |
winners | character varying(30) |
Distribution: Hash (seasonid)
premdb=# select * from season;
seasonid | season_name | winners
----------+--------------+-------------------
20 | 2011-2012 | Manchester City
21 | 2012-2013 | Manchester United
22 | 2013-2014 | Manchester City
23 | 2014-2015 | Chelsea
24 | 2015-2016 | Leicester City
(5 rows)
premdb=# select *, length(season_name) length_char, length(winners) length_varchar from season;
seasonid | season_name | winners | length_char | length_varchar
----------+--------------+-------------------+-------------+----------------
20 | 2011-2012 | Manchester City | 9 | 15
21 | 2012-2013 | Manchester United | 9 | 17
22 | 2013-2014 | Manchester City | 9 | 15
23 | 2014-2015 | Chelsea | 9 | 7
24 | 2015-2016 | Leicester City | 9 | 14
(5 rows)
Note that the length of the VARCHAR
strings (winners
)
varies. The length of the CHAR
strings (season_name
) is
consistently 9
, regardless of the fact that the column was defined as
CHAR(12)
.
premdb=# insert into season values(25, '2016-2017 ', 'Chelsea ');
INSERT 0 1
premdb=# insert into season values(26, '2017-2018 ', '');
premdb=# select *, length(season_name) length_char, length(winners) length_varchar from season;
seasonid | season_name | winners | length_char | length_varchar
----------+--------------+-------------------+-------------+----------------
20 | 2011-2012 | Manchester City | 9 | 15
21 | 2012-2013 | Manchester United | 9 | 17
22 | 2013-2014 | Manchester City | 9 | 15
23 | 2014-2015 | Chelsea | 9 | 7
24 | 2015-2016 | Leicester City | 9 | 14
25 | 2016-2017 | Chelsea | 9 | 9
26 | 2017-2018 | | 9 | 0
(7 rows)
Note that the season_name
column always returns a length of
9
. The trailing blanks for rows 25
and
26
were trimmed. Further, note that the lengths of the
Chelsea
values for rows 23
and 25
are
different. In row 25
, the trailing blanks are preserved, making a length of
9
, not 7
.
Now run some queries that compare these character strings with literal values:
premdb=# select * from season where winners='Chelsea';
seasonid | season_name | winners
----------+--------------+---------
23 | 2014-2015 | Chelsea
(1 row)
premdb=# select * from season where winners like 'Chelsea%';
seasonid | season_name | winners
----------+--------------+-----------
23 | 2014-2015 | Chelsea
25 | 2016-2017 | Chelsea
(2 rows)
premdb=# select * from season where season_name ='2016-2017 ';
seasonid | season_name | winners
----------+--------------+-----------
25 | 2016-2017 | Chelsea
(1 row)
season
table called
season_varchar
, in which both character columns are defined as
VARCHAR
columns.
premdb=# \d season_varchar
Table "public.season_varchar"
Column | Type | Modifiers
-------------+-----------------------+-----------
seasonid | smallint |
season_name | character varying(12) |
winners | character varying(30) |
Distribution: Hash (seasonid)
Insert
the same data in the new table, including the trailing blanks. You now have two tables that
appear to return identical result sets:
premdb=# select * from season_varchar;
seasonid | season_name | winners
----------+-------------+-------------------
20 | 2011-2012 | Manchester City
21 | 2012-2013 | Manchester United
22 | 2013-2014 | Manchester City
23 | 2014-2015 | Chelsea
24 | 2015-2016 | Leicester City
25 | 2016-2017 | Chelsea
26 | 2017-2018 |
(7 rows)
premdb=# select * from season;
seasonid | season_name | winners
----------+--------------+-------------------
20 | 2011-2012 | Manchester City
21 | 2012-2013 | Manchester United
22 | 2013-2014 | Manchester City
23 | 2014-2015 | Chelsea
24 | 2015-2016 | Leicester City
25 | 2016-2017 | Chelsea
26 | 2017-2018 |
(7 rows)
season_name
, note the
results:premdb=# select * from season s, season_varchar sv where s.season_name=sv.season_name order by s.seasonid;
seasonid | season_name | winners | seasonid | season_name | winners
----------+--------------+-------------------+----------+-------------+-------------------
20 | 2011-2012 | Manchester City | 20 | 2011-2012 | Manchester City
21 | 2012-2013 | Manchester United | 21 | 2012-2013 | Manchester United
22 | 2013-2014 | Manchester City | 22 | 2013-2014 | Manchester City
23 | 2014-2015 | Chelsea | 23 | 2014-2015 | Chelsea
24 | 2015-2016 | Leicester City | 24 | 2015-2016 | Leicester City
(5 rows)
Only 5
rows are returned, not 7
. The trailing blanks
preserved in the VARCHAR
version of the season_name
column
(rows 25
and 26
) cause a mismatch for those two rows.