PostgreSQL | Data Types | String Types (varchar, char, text)

Tags:

• PostgreSQL
• Database

This page explains character types among the data types available in PostgreSQL. Character types include variable-length character strings (character varying, varchar), fixed-length character strings (character, char), and unrestricted variable-length character strings (text).

String Types

The following character data types are available.

Type	Size	Alias
character varying(n)	variable-length string	varchar(n)
character(n)	fixed-length string	char(n)
text	unrestricted variable length

character varying(n) is a variable-length character type. It can store strings up to the maximum length n.

character(n) is a fixed-length character type. If the string to be stored has fewer than n characters, the remaining characters are padded with spaces.

For both character types, an error occurs if you try to store a string longer than n characters, but if the string is shorter than n, only those characters are stored.

If you specify only character varying without a length, it is treated as having no character limit. In contrast, if you specify only character without a length, it is treated as character(1).

The text type is a variable-length character type with no length limit. Use it when storing very long strings.

–

For example, create the following table.

devkuma=# create table strtest (str1 varchar(10), str2 char(10));
CREATE TABLE
devkuma=#

Add the following data to the created table.

devkuma=# insert into strtest values('kuma', 'kuma');
INSERT 0 1
devkuma=# insert into strtest values('  kuma  ', '  kuma  ');
INSERT 0 1
devkuma=#

The first row contains the string kuma in each column, and the second row contains the string kuma with two spaces before and after it. Now query the table. Use the char_length function to display the number of characters as well.

devkuma=# select str1, char_length(str1), str2, char_length(str2) from strtest;
   str1   | char_length |    str2    | char_length
----------+-------------+------------+-------------
 kuma     |           4 | kuma       |           4
   kuma   |           8 |   kuma     |           6
(2 rows)


devkuma=#

When the variable-length string (str1) is queried with SELECT, the stored string is returned as-is, including spaces before and after it. In contrast, for the fixed-length string (str2), spaces before the queried string remain, but trailing spaces after the string are removed.

–

This page explained character types among the data types available in PostgreSQL.