PostgreSQL Character Types: CHAR, VARCHAR, TEXT

PostgreSQL provides us with a wide number of data types for storing different kinds of values. However, data generated from the applications is majorly often in string format. However, these string values can also differ and only a single datatype doesn’t fit all the time for storing them. Therefore PostgreSQL provides us with three character data types- CHAR, VARCHAR, and TEXT.

In this tutorial, we will learn about these data types with examples. So without further ado, let’s get started!

Introduction to PostgreSQL Character Types

There are three character datatypes provided by PostgreSQL- CHAR(n), VARCHAR(n) and TEXT, where the n is a positive integer value for the string length. Note that, the CHAR(n) and VARCHAR(n) can also be specified as CHARACTER(n) and CHARACTER VARYING(n) respectively.

Look at the table below to get an idea about character types and their use.

CHAR Type in PostgreSQL

CHAR(n) or CHARACTER(n) is pretty much similar to the char in other database management systems. It is a fixed length string storing column type with a maximum capacity of specified limit n.

If you try to insert a string of length greater than n, you will get an error. In the case of excess spaces, the string will be truncated to the maximum length. For example, if the n is 2 and the string value is ‘ta ‘, all the excess spaces will get terminated and you will not get any error.

If the string length is less than the specified maximum length of CHAR, it will get stored as a space-padded value. For example, if the n is 4 and the string value is ‘ta’, the column will store this value as a space-padded value. However, you will not get space-padded values while retrieving them. Values will only occupy the maximum length limit, that’s why it is called a fixed-length string.

Note that, CHAR without any limit is a CHAR(1).

VARCHAR Type in PostgreSQL

VARCHAR(n) or CHARACTER VARYING(n) is similar to the CHAR(n) but it doesn’t space-pad the values if the string is shorter than the maximum limit. Therefore it is called a variable-length data type.

Note that, VARCHAR consumes only the space required for a string value that is getting inserted. Whereas, the CHAR type will consume a fixed amount of space all the time, no matter how short the value is.

If you don’t specify the maximum limit, VARCHAR will store values of any length.

TEXT Type in PostgreSQL

TEXT is just a VARCHAR without a maximum limit. You can use TEXT and VARCHAR alternatively.

As per the postgresql documentation,There is zero performance different among these types. Unlike the performace advantages of CHAR in other database systems, there is no any advantage over here in postgresql. Therefore, it is recommended to use VARCHAR or TEXT type in most situations.

PostgreSQL Character Types Examples

Now let’s see some examples of these three types.

Let’s log in to the psql shell first.

psql -U postgres

Now let’s create a table with three columns having different character types.

CREATE TABLE charTypesDemo(
id SERIAL PRIMARY KEY,
charType CHAR(5),
varcharType VARCHAR(5),
textType TEXT
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Example 1 – String length greater than the maximum limit

Then insert some data into the table.

INSERT INTO charTypesDemo(charType,varcharType,textType)
VALUES('post','postgresql', 'postgresql database');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

As you can see, the varchar type has a limit of 5 characters but the string value to be inserted is longer. Therefore we get an error saying “value too long”. The same happens with the CHAR type as well.

Example 2 – String longer than maximum limit but having excess space

Now let’s try inserting a string having a greater length than the maximum limit in the CHAR column, but the catch is that the string will contain the excess space instead of other characters.

INSERT INTO charTypesDemo(charType,varcharType,textType)
VALUES('p           ','postg', 'postgresql database');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, we have put 10 extra spaces. Let’s see if PostgreSQL accepts it or not.

As you can see, even if the string length is greater, PostgreSQL considers it valid because it trims all the excess spaces.

Example 3 – Valid values without spaces/extra characters

Now let’s insert valid values.

INSERT INTO charTypesDemo(charType,varcharType,textType)
VALUES('char','vchar', 'text type can contain too long text');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

As you can see, all values are valid and correct. Let’s see the result.

Perfect! As you can see, all the values are perfectly inserted.

Conclusion

In this tutorial, we have learned about the character types in PostgreSQL. It is worth noting that, char, varchar and text provide similar performance in PostgreSQL. Therefore, PostgreSQL recommends you use text or varchar type in most cases. I hope you have understood the topic and tried it yourself. We would recommend you play with these types using different values, such as with extra spaces, fewer spaces, additional characters etc and check the result.

Reference

PostgreSQL Official Documentation