PostgreSQL: Character Data Types

PostgreSQL supports character types such as CHARACTER VARYING (n) or VARCHAR(n), CHARACTER(n) or CHAR(n), and TEXT, where n is a positive integer.

CHAR(n) and VARCHAR(n) are both SQL-supported data types. TEXT is the one supported by PostgreSQL.

CHAR(n) and VARCHAR(n)

• Both CHAR(n) and VARCHAR(n) can store up to n characters.
• CHAR(n) is a fixed-length data type so if you store characters less than n in the column, it will pad the string with a blank before storing it.
• VARCHAR(n) is a variable length data type so if you store characters less than n in the column, it will store the string as it is.
• If you try to store more than n characters in CHAR(n) or VARCHAR(n), it will raise an error. However, if excessive characters are all spaces, then PostgreSQL truncates spaces up to length n and stores the characters.
• If you explicitly cast a string to CHAR(n) or VARCHAR(n), then PostgreSQL will truncate a string to n characters before storing them.
• If you specify CHAR without length (n), by default it will take as CHAR(1).
• If you specify VARCHAR without length (n), it behaves just like the TEXT data type.

Let's create Person table with CHAR, VARCHAR and TEXT fields.

CREATE TABLE Person (
	id serial PRIMARY KEY,
	name VARCHAR(15),
	gender CHAR,
	address TEXT
);

Then, insert a row in Product table like below

INSERT INTO person(name, gender, address)
VALUES('Anie Smith', 'Female',' Keaton Underwood Ap #636-8081 Arc Avenue Thiensville Maryland 19689');

PostgreSQL clearly raises an error as the value is too large for the CHARACTER(1). This is because we specified CHAR for gender column that set gender column as CHARACTER(1) by default and we are trying to insert 5 character value 'Female' to it.

Now let's try to insert name as 'Anie Smith ' with six trailing spaces after name and gender as 'F' with four trailing spaces like 'F ' and try to insert again.

INSERT INTO person(name, gender, address)
VALUES('Anie Smith      ', 'F    ',' Keaton Underwood Ap #636-8081 Arc Avenue Thiensville Maryland 19689');

This time value got inserted as PostgreSQL truncated trailing spaces for name and gender columns and stored only character values 'Anie Smith' as name and 'F' in the gender columns.

The above query result shows the data type of each column along with the data. You can see the address column is a text data type and we could store large text in it.