Skip to main content

PostgreSQL Data Types

PostgreSQL supports a wide range of data types. In this article, we will discuss some of the most commonly used data types in PostgreSQL.

Built-in Data Types​

PostgreSQL supports a wide range of built-in data types. Here are some of the most commonly used data types:

  • Numeric Types: INTEGER, BIGINT, DECIMAL, NUMERIC, REAL, DOUBLE PRECISION, SMALLINT
  • Character Types: CHAR, VARCHAR, TEXT
  • Binary Types: BYTEA
  • Date/Time Types: DATE, TIME, TIMESTAMP, INTERVAL
  • Boolean Type: BOOLEAN
  • Enum Types: ENUM
  • Array Types: ARRAY
  • JSON Types: JSON, JSONB
  • UUID Type: UUID
  • Network Address Types: INET, CIDR, MACADDR
  • Geometric Types: POINT, LINE, LSEG, BOX, PATH, POLYGON, CIRCLE
  • Bit String Types: BIT, BIT VARYING
  • XML Type: XML
  • Range Types: INT4RANGE, INT8RANGE, NUMRANGE, TSRANGE, TSTZRANGE, DATERANGE

Custom TYPE and DOMAIN​

We can create custom types using the CREATE TYPE statement. For example, we can create a custom type user_type to represent the user data.

CREATE TYPE user_profile_type AS (
    name VARCHAR(255),
    email VARCHAR(255),
    age INTEGER
);

We can use the user_type in the table definition.

CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    profile user_profile_type
);

A DOMAIN, essentially, is a domain can be seen as a "restricted" or "constrained" version of an existing type. It can be used to enforce constraints on the data that can be stored in a column. For example, we can create a domain for the email type to validate the email address.

CREATE DOMAIN email AS VARCHAR(255)
    CONSTRAINT valid_email CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');