PostgreSQL VARCHAR Type

Summary: in this tutorial, you’ll learn how to use PostgreSQL VARCHAR type to store variable-length strings in the database.

Getting Started with PostgreSQL VARCHAR Type #

The CHARACTER VARYING type, or VARCHAR, allows you to store variable-length strings in the database.

Here’s the syntax for defining a column with the CHARACTER VARYING type:

column_name CHARACTER VARYING (n)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In practice, you often use VARCHAR a synonym of the CHARACTER VARYING type:

column_name VARCHAR(n)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, specify the length specifier (n) that is the maximum number of characters the VARCHAR column can store. n is a positive integer and cannot exceed 10,485,760.

If you attempt to insert or update a string with more than n characters into the column, PostgreSQL will issue an error.

There are two main reasons you want to limit the length of the strings stored in the database:

• First, you want to create a validation rule at the database level so that the string length cannot exceed a certain length.
• Second, you want the app’s user interface broken due to a very long string.

Unlike the CHAR column, PostgreSQL does not pad spaces if the values stored in the VARCHAR column have a length of less than n.

If you want to store a string with any length, you can omit the length specifier in the column definition:

column_name VARCHARCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this case, the column_name can store strings of any length.

PostgreSQL VARCHAR type example #

First, create a products table:

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

The products table has two VARCHAR columns:

• The name column can store up to 50 characters. If you attempt to insert or update strings with a length greater than 50, PostgreSQL will issue an error.
• The description column can store a string of any length.

The name column has a length limit to ensure product names can be displayed clearly on receipts. Without this limit, product names may overlap with other information.

Second, insert some rows into the products table:

INSERT INTO
  products (name, description)
VALUES
  (
    'Galaxy S21',
    'Introducing a high-end smartphone with a 6.2-inch screen, triple camera, and 5G connectivity.'
  ),
  (
    'iPhone 16',
    'Introducing the Apple latest model featuring a 6.1-inch Super Retina XDR display and A18 Bionic chip.'
  ) RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 id |    name    |                                              description
----+------------+-------------------------------------------------------------------------------------------------------
  1 | Galaxy S21 | Introducing a high-end smartphone with a 6.2-inch screen, triple camera, and 5G connectivity.
  2 | iPhone 16  | Introducing the Apple latest model featuring a 6.1-inch Super Retina XDR display and A18 Bionic chip.Code language: JavaScript (javascript)

Third, attempt to insert a new product with a name of more than 50 characters.

INSERT INTO products(name)
VALUES( 'Ultra-High Definition Quantum Dot Curved Smart TV 90-Inch');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL issued the following error:

value too long for type character varying(50)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the VARCHAR type to store variable-length strings in the database.
• Use the VARCHAR without a length specifier to store a string of any length.

Quiz #