Data types

A data type is the single most important decision you make about a column. It controls what values fit, how much space they take, how they sort and compare, and which operators work on them. PostgreSQL ships with a rich set of built-in types and even lets you define your own. This lesson tours the ones you will reach for most often, with short examples you can run.

Numbers

For whole numbers, integer (four bytes, up to about two billion) covers most counters and ids, while bigint (eight bytes) handles larger ranges. For values where exactness matters — money above all — use numeric, which stores decimals precisely. The floating-point type real is fast but approximate, so keep it for scientific or measurement data where tiny rounding is acceptable.

CREATE TABLE measurements (
  count_int  integer,
  count_big  bigint,
  price      numeric(10, 2),
  reading    real
);

INSERT INTO measurements VALUES (42, 9000000000, 19.99, 3.14);
SELECT * FROM measurements;

 count_int | count_big  | price | reading
-----------+------------+-------+---------
        42 | 9000000000 | 19.99 |    3.14
(1 row)

Text

PostgreSQL has two everyday string types. text holds a string of any length, and varchar(n) holds a string up to n characters. There is no performance penalty for text; the length limit on varchar is the only real difference, and you can always enforce a length with a CHECK constraint instead.

CREATE TABLE notes (
  title  varchar(120),
  body   text
);

INSERT INTO notes VALUES ('Release plan', 'Ship the new dashboard on Friday.');
SELECT title, body FROM notes;

    title     |               body
--------------+-----------------------------------
 Release plan | Ship the new dashboard on Friday.
(1 row)

Booleans

A boolean column holds true, false, or null. PostgreSQL accepts several spellings on input — true, 't', 'yes', 1 and their false equivalents — but always displays the value as t or f.

SELECT true AS yes, false AS no, null::boolean AS unknown;

 yes | no | unknown
-----+----+---------
 t   | f  |
(1 row)

Dates and times

For a calendar day with no time, use date. For a moment in time, use timestamptz — short for timestamp with time zone — which stores the instant in UTC and converts to and from your session’s time zone automatically. The plain timestamp type, without the zone, looks similar but loses that context and is a common source of bugs.

SELECT
  current_date                   AS today,
  now()                          AS moment,
  date '2026-01-31'              AS a_day;

   today    |             moment              |   a_day
------------+-------------------------------+------------
 2026-06-25 | 2026-06-25 09:14:22.51+00      | 2026-01-31
(1 row)

UUIDs

A uuid stores a 128-bit universally unique identifier. UUIDs make good primary keys when ids must be generated by clients or stay unguessable, since they do not reveal row counts the way a sequence does.

SELECT gen_random_uuid() AS id;

                  id
--------------------------------------
 6f3a1b2c-9d4e-4f70-8a1b-2c3d4e5f6a7b
(1 row)

JSON with jsonb

The jsonb type stores JSON in a decomposed binary form that is fast to query and index. Prefer it over the plain json type, which keeps the raw text and re-parses on every access. You can pull values out with the arrow operators.

CREATE TABLE events (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payload jsonb
);

INSERT INTO events (payload)
VALUES ('{"kind": "signup", "plan": "pro"}');

SELECT payload ->> 'plan' AS plan FROM events;

 plan
------
 pro
(1 row)

Arrays

Any type can become an array by adding square brackets, so int[] is a column of integer arrays. Arrays suit small, ordered lists that belong to a single row, such as tags. Array literals are written with curly braces inside a quoted string.

CREATE TABLE articles (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tags text[]
);

INSERT INTO articles (tags) VALUES ('{"sql", "postgres", "intro"}');
SELECT tags, tags[1] AS first_tag FROM articles;

          tags          | first_tag
------------------------+-----------
 {sql,postgres,intro}   | sql
(1 row)

Enumerated types

When a column may hold only a fixed set of labels, an enum makes the allowed values part of the type. You define it once with CREATE TYPE, then use it like any built-in type. Values sort in the order you list them.

CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'cancelled');

CREATE TABLE orders (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status order_status NOT NULL DEFAULT 'pending'
);

INSERT INTO orders (status) VALUES ('paid');
SELECT id, status FROM orders;

 id | status
----+--------
  1 | paid
(1 row)

In pgAdmin: custom types created with CREATE TYPE appear under Schemas then public then Types in the browser tree, alongside the Tables node. You can review an enum’s allowed labels there without writing a query.

Tips / gotchas

Use timestamptz, not timestamp, for any moment in time. Storing in UTC and converting per session avoids whole categories of time-zone bugs; reach for plain timestamp only when a value genuinely has no zone, like a recurring local alarm.
Reach for text by default and use varchar(n) only when a real maximum length is part of the rule. There is no speed difference, and a CHECK constraint can enforce a limit on text if you ever need one.
Store money in numeric, never in real or double precision. Binary floating point cannot represent values like 0.10 exactly, which leads to rounding errors that accumulate.
Prefer jsonb over json. The binary form is faster to query and can be indexed; the text json type is mainly useful when you must preserve the exact input formatting.
An enum is great for a truly fixed set of labels, but adding or reordering values later needs an ALTER TYPE. If the set changes often, a small lookup table with a foreign key is more flexible.