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:
[email protected]:datacomy> alter table my_char_table add my_char_4_col char(4);
[email protected]: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:
[email protected]: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:
[email protected]: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:
[email protected]:datacomy> insert into my_char_table(my_char_4_col) values('dog ');
INSERT 0 1
[email protected]: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:
[email protected]:datacomy> create table my_char_table_2(my_char_col char);
CREATE TABLE
Time: 0.005s
[email protected]: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
[email protected]: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).
[email protected]:datacomy> create table my_varchar_table_1(my_varchar_col_no_n varchar);
CREATE TABLE
Time: 0.008s
[email protected]: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:
[email protected]:datacomy> create table my_varchar_table_2(my_varchar_col_n_3 varchar(3));
CREATE TABLE
[email protected]: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
[email protected]: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:
[email protected]:datacomy> create table my_text_table(text_col text);
CREATE TABLE
Time: 0.009s
[email protected]: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