Data Types in PostgreSQL

Choosing the right data type for each column is one of the most important decisions when designing a PostgreSQL schema. Using the correct type improves storage efficiency, query performance, and data integrity.

Numeric Types

Type	Storage	Range / Notes
smallint	2 bytes	-32,768 to 32,767
integer	4 bytes	-2,147,483,648 to 2,147,483,647
bigint	8 bytes	±9.2 × 10^18
numeric(p,s)	Variable	Exact arbitrary-precision decimal
real	4 bytes	6 decimal digits precision
double precision	8 bytes	15 decimal digits precision
serial / bigserial	4 / 8 bytes	Auto-incrementing integers

Use numeric for money and other values where floating-point rounding is unacceptable.

Character Types

Type	Description
char(n)	Fixed-length, padded with spaces
varchar(n)	Variable-length with a maximum
text	Unlimited variable-length string

In PostgreSQL text and varchar without a limit have identical performance. Prefer text unless you genuinely need to enforce a maximum length at the database level.

Boolean

CREATE TABLE feature_flags (
  name    text    NOT NULL,
  enabled boolean NOT NULL DEFAULT false
);

PostgreSQL accepts true/false, yes/no, on/off, and 1/0 as boolean literals.

Date and Time Types

Type	Description
date	Calendar date (year, month, day)
time	Time of day (no timezone)
timestamp	Date and time (no timezone)
timestamptz	Date and time with timezone (recommended)
interval	A span of time

Always prefer timestamptz over timestamp for storing moments in time so that PostgreSQL correctly converts between timezones.

PostgreSQL-Specific Types

PostgreSQL extends the SQL standard with types not found in most other databases:

• uuid — universally unique identifiers (excellent for distributed primary keys)
• jsonb — binary JSON, indexed and highly queryable
• array — any type can be made into an array (e.g. integer[], text[])
• inet / cidr — IP addresses and network blocks
• tsvector / tsquery — full-text search

-- Using uuid and jsonb
CREATE TABLE users (
  id      uuid    PRIMARY KEY DEFAULT gen_random_uuid(),
  profile jsonb,
  tags    text[]
);