PostgreSQL: Character Data Types
PostgreSQL supports character types such as
CHARACTER VARYING (n) or
n is a positive integer.
VARCHAR(n) are both SQL-supported data types.
TEXT is the one supported by PostgreSQL.
|Character Data Type||Description|
|CHARACTER VARYING (n) or VARCHAR(n)||Variable length with limit|
|CHARACTER(n) or CHAR(n)||Fixed length with blank padded|
|TEXT, VARCHAR||Variable unlimited length|
CHAR(n) and VARCHAR(n)
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
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
VARCHAR(n), then PostgreSQL will truncate a string to n characters before storing them.
If you specify
CHARwithout length (n), by default it will take as
If you specify
VARCHARwithout length (n), it behaves just like the
Person table with
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
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
'Anie Smith ' with six trailing spaces after
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
gender columns and stored only character values
'Anie Smith' as
'F' in the
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.