Skip to content
Related Articles

Related Articles

PostgreSQL – Difference between CHAR, VARCHAR and TEXT
  • Last Updated : 28 Aug, 2020

Now that we are familiar with the concept of character data types CHAR, VARCHAR, and TEXT respectively in PostgreSQL, this article will focus on highlighting the key difference between them.

What is CHAR data type?
This data type is used to store character of limited length. It is represented as char(n) or character(n) in PostgreSQL, where n represents the limit of the length of the characters.If n is not specified it defaults to char(1) or character(1).

What is VARCHAR data type?
This data type is used to store characters of limited length. It is represented as varchar(n) in PostgreSQL, where n represents the limit of the length of the characters. If n is not specified it defaults to varchar which has unlimited length.

What is TEXT data type?
This data type is used to store character of unlimited length. It is represented as text in PostgreSQL. The performance of the varchar (without n) and text are the same.

The below table provides with the major difference between CHAR, VARCHAR and TEXT:



ComparisonCHARVARCHARTEXT
SyntaxCHAR(n)VARCHAR(n)TEXT
Representationchar(n) or character(n)varchar(n)text
Length of charactersLimitedLimitedUnlimited
Spaces and paddingGets truncatedGets truncatedRenains intact
If n not specifiedn = 1n = infinityConcept of n doesn’t exist
Query cycleExtra cycle to check lengthExtra cycle to check lengthNo evaluation of character length

Example:
Let’s create a new table(say, character_tests) for the demonstration using the below commands:

CREATE TABLE character_tests (
    id serial PRIMARY KEY,
    x CHAR (1),
    y VARCHAR (10),
    z TEXT
);

Now let’s insert a new row into the char_test table using the below command:

INSERT INTO character_tests (x, y, z)
VALUES
    (
        'Geeks',
        'This is a test for varchar',
        'This is a very long text for the PostgreSQL text column'
    );

At this stage PostgreSQL will raise an error as the data type of the x column is char(1) and we tried to insert a string with three characters into this column as shown below:

ERROR:  value too long for type character(1)

So, now let’s fix it.

INSERT INTO character_tests (x, y, z)
VALUES
    (
        'G',
        'This is a test for varchar',
        'This is a very long text for the PostgreSQL text column'
    );

Now, we will get the same error for the y column as the number of characters entered is greater than 10 as shown below:

ERROR:  value too long for type character(10)

Let’s fix it too.

INSERT INTO character_tests (x, y, z)
VALUES
    (
        'Y',
        'varchar(n)',
        'This is a very long text for the PostgreSQL text column'
    );

Now that we have managed to successfully assign the values to the character data type, check it by running the below command:

SELECT * FROM character_tests;

Output:

My Personal Notes arrow_drop_up
Recommended Articles
Page :