Which Character Data Types in YSQL Preserve Preceding or Trailing Spaces?

Jim Knicely

The following character data types are supported in YugabyteDB YSQL:

  • varchar(n): variable-length string
  • char(n): fixed-length, blank padded
  • text, varchar: variable unlimited length

While on site with a customer today, a question came up as to which of these data types would retain leading space(s) in from of a string value or lagging space(s) following a string value when inserting data into a table.

Let’s find out…

First, let’s check leading spaces:

				
					yugabyte=# CREATE TABLE t_leading(c_varchar VARCHAR, c_char CHAR(4), c_text TEXT);
CREATE TABLE

yugabyte=# INSERT INTO t_leading VALUES ('   A', '   A', '   A');
INSERT 0 1

yugabyte=# SELECT c_varchar, LENGTH(c_varchar),
yugabyte-#        c_char, LENGTH(c_char),
yugabyte-#        c_text, LENGTH(c_text)
yugabyte-#   FROM t_leading;
 c_varchar | length | c_char | length | c_text | length
-----------+--------+--------+--------+--------+--------
    A      |      4 |    A   |      4 |    A   |      4
(1 row)

All three data types, VARCHAR, CHAR and TEXT, retain the spaces in front of the string.

Next let’s check on trailing spaces:

				
					yugabyte=# TRUNCATE TABLE t_leading;
TRUNCATE TABLE

yugabyte=# INSERT INTO t_leading VALUES ('A   ', 'A   ', 'A   ');
INSERT 0 1

yugabyte=# SELECT c_varchar, LENGTH(c_varchar),
yugabyte-#        c_char, LENGTH(c_char),
yugabyte-#        c_text, LENGTH(c_text)
yugabyte-#   FROM t_leading;
 c_varchar | length | c_char | length | c_text | length
-----------+--------+--------+--------+--------+--------
 A         |      4 | A      |      1 | A      |      4
(1 row)

In this case, only the VARCHAR and TEXT data types preserve the spaces, while CHAR trims them!

Have Fun!