Data Types (PostgreSQL)

PostgreSQL

Schemas

Numbers

Integers

SMALLINT (also aliased to INT2)
INTEGER (aliased as INT4)+
BIGINT (aliased as INT8)

Numeric

NUMERIC(precision, scale)

Can hold values bigger than BIGINT

Floating Point

REAL
DOUBLE PRECISION

NaN

Infinity

Constraints

Check Constraint

A check constraint is the most generic constraint type. It allows you to specify that the value in a certain column must satisfy a Boolean (truth-value) expression.

  CREATE TABLE purchase_item (
    price INTEGER CONSTRAINT positive_price CHECK (price > 0),
    discount INTEGER CONSTRAINT non_negative_discount CHECK (discount >= 0),
    description TEXT (LENGTH(description) < 2048)
  );

You can also apply a CHECK constraint to the entire table and can involve multiple columns.

  CREATE TABLE purchase_item (
    price INTEGER CONSTRAINT positive_price CHECK (price > 0),
    discount INTEGER CONSTRAINT non_negative_discount CHECK (discount >= 0),
    description TEXT (LENGTH(description) < 2048),
    CHECK (price > discount)
  );

Unique Constraints

Ensures that the data contained in a column (or in a group of columns) is unique.

  CREATE TABLE products (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    brand TEXT UNIQUE NOT NULL,
    product_no TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
  );
  -- or
  CREATE TABLE products (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    brand TEXT NOT NULL,
    product_no TEXT NOT NULL,
    name TEXT NOT NULL,
    UNIQUE (brand, product_no)
  );

Exclude Constraints

  CREATE EXTENSION IF NOT EXISTS btree_gist;
  CREATE TABLE bookings (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    -- TODO: use proper enums for this in real life
    status TEXT NOT NULL;
    -- The time the resource will be allocated to someone else
    reservation_period TSRANGE;
    -- For a given booking, there can be no overlaps, unless
    -- the current booking status is set to 'cancelled'.
    EXCLUDE USING gist (id WITH =, reservation_period WITH &&) WHERE (reservation_status != 'cancelled')
  );

Primary Key Constraint

Foreign Key Constraints

A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table. We say this maintains the referential integrity between two related tables.

  CREATE TABLE products (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    brand TEXT NOT NULL,
    product_no TEXT NOT NULL,
    name TEXT NOT NULL,
    UNIQUE (brand, product_no)
  );

  CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id BIGINT REFERENCES products(id),
    quantity INTEGER order_must_be_positive CHECK (quantity > 0)
  );

Domain Types

  CREATE DOMAIN us_postal_code AS TEXT
    CHECK(VALUE ~ '^\d{5}$' OR VALUE ~ '^\d{5}-\d{4}$'
  );

Binary Data

UUIDs

Booleans

Enums

  CREATE TYPE mood AS ENUM ('sad', 'neutral', 'happy');

  -- You can also add another value later, and set its position
  ALTER TYPE mood ADD VALUE 'afraid' AFTER 'sad';

Removing a value from an ENUM is tricky, you need to drop the old type and recreate a new one, making sure all tables default the old value to something else.

  -- Always do it inside a transaction
  BEGIN;

  -- Rename old type
  ALTER TYPE mood RENAME TO mood_old;

  -- Create a new type
  CREATE TYPE mood AS ENUM ('sad', 'neutral', 'happy');

  -- Update any tables that use this type, default old
  -- removed value to something else.
  UPDATE
    <table_with_enum>
  SET
    current_mood = 'neutral'
  WHERE
    current_mood NOT IN ('sad', 'neutral', 'happy');

  -- Switch to the new type
  ALTER TABLE <table_with_enum>
  ALTER COLUMN current_mood
    TYPE mood USING mood_old::text::status_enum;

  COMMIT;

Timestamps

Built-In Functions

to_timestap

Setting a DB Timezone

  SET TIME ZONE 'UTC';

Dates & Time

Magic Constants

CURRENT_DATE
CURRENT_TIME
CURRRENT_TIMESTAMP
LOCALTIME
LOCALTIMESTAMP

Intervals

Serial

Sequences

  CREATE SEQUENCE IF NOT EXISTS some_seq
    AS BIGINT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1;

SELECT NEXTVAL('some_seq');
SELECT CURRVAL('some_seq');

Outputs the current value of the session, not the global sequence value.
SELECT SETVAL('some_seq', 1000);

Identity

  CREATE TABLE user (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ...,
  );

  -- To find out the internal name of the sequence
  -- being used.
  SELECT pg_get_serial_sequence('user', 'id');

Network Types

JSON

JSON (PostgreSQL)

Arrays

PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, composite type, range type, or domain can be created.

Declaration

CREATE TABLE tictactoe (
  squares INTEGER[3][3]
);

Arrays indexes start at 1, not 0.

Generated Columns

A generated column is a special column that is always computed from other columns. Thus, it is for columns what a view is for tables.

  CREATE TABLE people (
    ...,
    height_cm numeric,
    -- The keyword STORED must be specified to choose the
    -- stored kind of generated column.
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
  );

Text Search Types

Check Full Text Search (PostgreSQL).

Bit String

Ranges

PostgreSQL comes with the following built-in range types:

Type	Data Type
`int4range`	`INTEGER`
`int8range`	`BIGINT`
`numrange`	`NUMERIC`
`tsrange`	`TIMESTAMP`
`tstzrange`	`TIMESTAMP WITH TIMEZONE`
`daterange`	`DATE`

  CREATE TABLE reservation (room int, during tsrange);
  INSERT INTO reservation VALUES
      (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');

  -- Containment
  SELECT int4range(10, 20) @> 3;

  -- Overlaps
  SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);

  -- Extract the upper bound
  SELECT upper(int8range(15, 25));

  -- Compute the intersection
  SELECT int4range(10, 20) * int4range(15, 25);

  -- Is the range empty?
  SELECT isempty(numrange(1, 5));