PostgreSQL Character Types: CHAR, VARCHAR and TEXT
There are three PostgreSQL character types:
- char: fixed length.
- varchar: variable length with optional limit.
- text: variable length without limit.
char and varchar need an argument n to specify the characters length or limit.
char(n)
The char(n) type stores fixed length strings.
If the string you are adding is shorter than n, spaces are added to make up the difference. For example, if ‘n’ is 7 and the string is house, which has 5 characters, the following value will be added: house . Note the two blank spaces. When comparing values, spaces at the end are ignored.
To create a new character column:
datacomy@localhost:datacomy> alter table my_char_table add my_char_4_col char(4);
datacomy@localhost:datacomy> select COLUMN_NAME,DATA_TYPE,character_maximum_length from INFORMATI
ON_SCHEMA."columns" where TABLE_NAME = 'my_char_table';
+---------------+-------------+----------------------------+
| column_name | data_type | character_maximum_length |
|---------------+-------------+----------------------------|
| my_char_4_col | character | 4 |
+---------------+-------------+----------------------------+
SELECT 1
If the string you are adding is longer than n, PostgreSQL with throw an error, unless all the excess characters are all spaces, in which case the string will be truncated to n.
Examples:
Adding a longer string:
datacomy@localhost:datacomy> insert into my_char_table(my_char_4_col) values('my house');
value too long for type character(4)
Adding a string shorter than n:
datacomy@localhost:datacomy> select * from my_char_table;
+-----------------+
| my_char_4_col |
|-----------------|
| car |
+-----------------+
SELECT 1
Adding a string longer than n but with all excess characters as spaces:
datacomy@localhost:datacomy> insert into my_char_table(my_char_4_col) values('dog ');
INSERT 0 1
datacomy@localhost:datacomy> select * from my_char_table;
+-----------------+
| my_char_4_col |
|-----------------|
| car |
| dog |
+-----------------+
SELECT 2
If n is not specified when creating a column, a column with the default value for n will be created. The default value for n in char is 1.
Example:
datacomy@localhost:datacomy> create table my_char_table_2(my_char_col char);
CREATE TABLE
Time: 0.005s
datacomy@localhost:datacomy> select COLUMN_NAME,DATA_TYPE,character_maximum_length from INFORM
ATION_SCHEMA."columns" where TABLE_NAME = 'my_char_table_2';
+---------------+-------------+----------------------------+
| column_name | data_type | character_maximum_length |
|---------------+-------------+----------------------------|
| my_char_col | character | 1 |
+---------------+-------------+----------------------------+
SELECT 1
datacomy@localhost:datacomy> insert into my_char_table_2(my_char_col) values('car');
value too long for type character(1)
varchar(n)
varchar(n) is used to specify a fixed length character type of length n. If n is not specified, the type will accept strings of any size shorter than 10485760 (about 1GB).
datacomy@localhost:datacomy> create table my_varchar_table_1(my_varchar_col_no_n varchar);
CREATE TABLE
Time: 0.008s
datacomy@localhost:datacomy> select COLUMN_NAME,DATA_TYPE,character_maximum_length from INFORM
ATION_SCHEMA."columns" where TABLE_NAME = 'my_varchar_table_1';
+---------------------+-------------------+----------------------------+
| column_name | data_type | character_maximum_length |
|---------------------+-------------------+----------------------------|
| my_varchar_col_no_n | character varying | <null> |
+---------------------+-------------------+----------------------------+
If you specify n and try to insert a string longer than n, PostgreSQL with throw an error:
datacomy@localhost:datacomy> create table my_varchar_table_2(my_varchar_col_n_3 varchar(3));
CREATE TABLE
datacomy@localhost:datacomy> select COLUMN_NAME,DATA_TYPE,character_maximum_length from INFORM
ATION_SCHEMA."columns" where TABLE_NAME = 'my_varchar_table_2';
+--------------------+-------------------+----------------------------+
| column_name | data_type | character_maximum_length |
|--------------------+-------------------+----------------------------|
| my_varchar_col_n_3 | character varying | 3 |
+--------------------+-------------------+----------------------------+
SELECT 1
datacomy@localhost:datacomy> insert into my_varchar_table_2(my_varchar_col_n_3) values('house'
);
value too long for type character varying(3)
text
text is used to store string of variable unlimited length. Although text is not in the SQL standard, several database management systems have it as well.
The following RDBMSs support text:
- MariaDB
- MySQL
- SQLite
The following RDBMS do not support text:
- Oracle
The following RDBMs will remove support for text:
datacomy@localhost:datacomy> create table my_text_table(text_col text);
CREATE TABLE
Time: 0.009s
datacomy@localhost:datacomy> select COLUMN_NAME,DATA_TYPE,character_maximum_length from INFORM
ATION_SCHEMA."columns" where TABLE_NAME = 'my_text_table';
+---------------+-------------+----------------------------+
| column_name | data_type | character_maximum_length |
|---------------+-------------+----------------------------|
| text_col | text | <null> |
+---------------+-------------+----------------------------+
SELECT 1
Comments and Recommendations
char is rarely used. It usually requires more storage space vs varchar and text, because it includes the space padding on the right side. Do not use char unless you know what your are doing.
From the docs:
There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead.
A length limit n to varchar doesn’t improve performance. So in many situations, text might be preferred.
If you need to stay compatible with other RDMSs which don’t have text, you can use varchar without a length limit as it doesn’t add performance. Additionally, varchar(n)
If you need to enforce a maximum length, you may add a check constraint:
References:
https://www.postgresql.org/docs/current/datatype-character.html