PostgreSQL TEXT Data Type

Summary: in this tutorial, you’ll learn how to use PostgreSQL TEXT data type to store variable-length character data.

Getting started with PostgreSQL TEXT data type #

The PostgreSQL TEXT data type is a variable-length character data type. The TEXT data type can handle large amounts of text, making it ideal for storing descriptions, documents, and comments.

Here’s the syntax for defining a table column with TEXT data type:

CREATE TABLE table_name {
    column_name TEXT,
    ...
}Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The main features of the TEXT data type column are:

• Variable length: The TEXT column can store strings of any length, up to 1GB. 1GB is the field length limit in PostgreSQL, not only TEXT field.
• Trailing spaces: The TEXT column retains trailing spaces and does not trim them upon retrieval. These trailing spaces are significant when making comparisons.
• Performance: The TEXT type has the same performance as the VARCHAR type.

In real-world scenarios, the TEXT data type is preferred for storing variable-length character data with no length limit, allowing you to handle any character data size.

Note that VARCHAR, without a length specifier, behaves like TEXT.

PostgreSQL TEXT data type example #

First, create a table called products to store the product information:

CREATE TABLE products (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  description TEXT
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this products table, the description column has a TEXT data type.

Second, insert a product into the products table:

INSERT INTO
  products (name, description)
VALUES
  (
    'iPhone 17',
    'Introducing a new AI smartphone a 6.2-inch screen, triple camera, and 5G connectivity.'
  ) RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 id |   name    |                                      description
----+-----------+----------------------------------------------------------------------------------------
  1 | iPhone 17 | Introducing a new AI smartphone a 6.2-inch screen, triple camera, and 5G connectivity.Code language: plaintext (plaintext)

Summary #

• Use PostgreSQL TEXT data type to store variable-length character data.

Quiz #