PostgreSQL Data Types

PostgreSQL has a rich set of data types. Choosing the right one keeps your data correct and your queries fast.

Numbers

Type	Use
`SMALLINT`	Small whole numbers, -32,768 to 32,767
`INTEGER` (or `INT`)	Whole numbers up to about 2.1 billion
`BIGINT`	Very large whole numbers
`NUMERIC(p, s)`	Exact decimals; use for money
`REAL`, `DOUBLE PRECISION`	Approximate decimals, for measurements

Example

sql

SELECT 10 / 4 AS whole, ROUND(10 / 4.0, 1) AS exact, 10::NUMERIC(6, 2) AS money;

Output

 whole | exact | money
-------+-------+-------
     2 |   2.5 | 10.00
(1 row)

Dividing two integers gives an integer, so 10 / 4 is 2. Use a decimal number to get 2.5.

:: is PostgreSQL's short way to cast (convert) a value to another type. '42'::INTEGER turns the text '42' into the number 42. The standard form CAST('42' AS INTEGER) also works.

Text

Type	Use
`TEXT`	Text of any length
`VARCHAR(n)`	Text with a maximum length of n
`CHAR(n)`	Fixed length text, padded with spaces

In PostgreSQL TEXT and VARCHAR are equally fast. Many developers use TEXT everywhere, and add VARCHAR(n) only when a maximum length is a real rule, such as a 20-character username.

True and False

PostgreSQL has a real BOOLEAN type with the values TRUE, FALSE and NULL (unknown).

Example

sql

CREATE TABLE notices (
  title TEXT,
  is_published BOOLEAN DEFAULT FALSE
);

SELECT title FROM notices WHERE is_published;

Because the column is already true or false, WHERE is_published is enough.

Dates and Times

Type	Example
`DATE`	`2026-10-15`
`TIME`	`14:30:00`
`TIMESTAMP`	`2026-10-15 14:30:00` (no time zone)
`TIMESTAMPTZ`	`2026-10-15 14:30:00+05` (with time zone)
`INTERVAL`	`3 months`, `2 days 4 hours`

Pakistan time is UTC+5, which is why the example shows +05.

Example

sql

SELECT CURRENT_DATE AS today,
       CURRENT_DATE + INTERVAL '3 months' AS course_ends;

Output

   today    |     course_ends
------------+---------------------
 2026-10-15 | 2027-01-15 00:00:00
(1 row)

For "created at" columns, use TIMESTAMPTZ. It stores the exact moment, and each user can see it in their own time zone.

UUID

A UUID is a 128-bit random ID such as 9f1c2b9e-6a3d-4c2e-8f2a-0b7d5e1a4c33. It is useful when IDs should not be guessable. PostgreSQL can create one with gen_random_uuid().

Arrays

Any column can be a list of values by adding []:

Example

sql

CREATE TABLE teachers (
  name TEXT,
  languages TEXT[]
);

INSERT INTO teachers VALUES ('Sir Kamran', ARRAY['Urdu', 'English']);

SELECT name FROM teachers WHERE 'Urdu' = ANY(languages);

Output

    name
------------
 Sir Kamran
(1 row)

JSON and JSONB

JSONB stores JSON documents in a fast binary form that can be searched and indexed. You will use it in the advanced lesson.

Example

sql

CREATE TABLE settings (
  data JSONB
);

INSERT INTO settings VALUES ('{"theme": "dark", "language": "ur"}');

Quick Guide

Data	Good type
ID	`INTEGER GENERATED ALWAYS AS IDENTITY` or `UUID`
Price	`NUMERIC(10, 2)`
Name, email	`TEXT`
Yes/no	`BOOLEAN`
Birthday	`DATE`
Created at	`TIMESTAMPTZ`
Tags	`TEXT[]`
Flexible extra data	`JSONB`

Exercise

What does SELECT 7 / 2; return in PostgreSQL?

3.534An error