PostgreSQL – Difference between CHAR, VARCHAR and TEXT

When working with textual data in PostgreSQL, choosing the appropriate character data type is essential for performance and data integrity. PostgreSQL offers three primary character data types: ‘CHAR’, ‘VARCHAR’, and ‘TEXT’. While these data types might seem similar, they have distinct differences that impact how they store and manage string data.

What is CHAR data type? 

The CHAR data type is used to store fixed-length strings. It is represented as ‘CHAR(n)’ or ‘CHARACTER(n)’ in PostgreSQL, where n specifies the length of the string. If ‘n’ is not provided, it defaults to ‘CHAR(1)’. The CHAR data type pads the stored value with spaces if the input is shorter than the defined length.

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. Unlike ‘CHAR‘, ‘VARCHAR‘ does not pad stored values with extra spaces.

What is TEXT data type? 

This data type is used to store characters of unlimited length. It is represented as text in PostgreSQL. The performance of the varchar (without n) and text are the same.  There is no specific length restriction, making it suitable for storing large text blocks like articles, descriptions, or logs.

Key Differences Between CHAR, VARCHAR, and TEXT in PostgreSQL

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

Example: Demonstrating CHAR, VARCHAR, and TEXT in PostgreSQL

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:  

Conclusion

In conclusion, choosing between ‘CHAR’, ‘VARCHAR’, and ‘TEXT’ in PostgreSQL depends on the specific requirements of your application, that is, use ‘CHAR’ for fixed-length data where padding is acceptable, use ‘VARCHAR’ for variable-length data with defined constraints and use ‘TEXT’ for unlimited-length strings or when there is no clear maximum length.