PostgreSQL

PostgreSQL Notes
Complete Reference Guide
Data Types & Constraints

Database Essentials

Quick Reference Handbook

Comprehensive Guide for Developers

Table of Contents

Part 1: PostgreSQL Data Types

1.1 Numeric Types (Most Used)

1.2 Character Types (Most Used)

1.3 Date/Time Types (Most Used)

1.4 Boolean Type (Most Used)

1.5 Other Numeric Types

1.6 Binary Data Types

1.7 Geometric Types

1.8 Network Address Types

1.9 Text Search Types

1.10 UUID Type

1.11 XML & JSON Types

1.12 Array Type

1.13 Range Types

1.14 Other Special Types

Part 2: PostgreSQL Constraints

2.1 PRIMARY KEY

2.2 FOREIGN KEY

2.3 UNIQUE

2.4 NOT NULL

2.5 CHECK

2.6 DEFAULT

2.7 EXCLUDE
Part 1: PostgreSQL Data Types

Part 1: PostgreSQL Data Types

PostgreSQL offers a rich set of built-in data types. This section is organized into
two categories: Most Used Types (commonly used in everyday database design)
and Other Types (specialized types for specific use cases).

1.1 Numeric Types (Most Used)

Table 1 Most Commonly Used Numeric Data Types

Data Type Description Common Use Cases

INTEGER Signed 4-byte integer. Range: Primary keys (auto-increment

or INT -2,147,483,648 to +2,147,483,647 IDs), counters, age, quantity,
foreign key references, row
counts

BIGINT Signed 8-byte integer. Range: Large ID values, high-volume

-9,223,372,036,854,775,808 to transaction IDs, timestamps in
+9,223,372,036,854,775,807 milliseconds, big data analytics,
social media user IDs

SERIAL Auto-incrementing 4-byte integer Auto-generated primary keys,

(INTEGER with auto-increment) order numbers, invoice IDs,
auto-numbering columns

BIGSERIAL Auto-incrementing 8-byte integer Auto-generated primary keys

(BIGINT with auto-increment) for high-volume tables,
distributed systems requiring
large ID space

NUMERIC(p,s) Exact numeric with selectable Monetary values, financial

or DECIMAL(p,s) precision. p = precision, s = scale calculations, prices, tax
amounts, currency exchange
rates, accounting data

4
Part 1: PostgreSQL Data Types

REAL Single precision Scientific calculations, sensor readings,

floating-point (4 approximate measurements, graphics
bytes) coordinates, temperature readings

DOUBLE PRECISION Double precision Scientific computations requiring higher

floating-point (8 precision, GPS coordinates, complex
bytes) mathematical operations

Best Practice
Use NUMERIC for money/financial data to avoid floating-point precision errors.
Use INTEGER for most IDs, BIGINT only when you expect more than 2 billion
rows.

1.2 Character Types (Most Used)

Table 2 Most Commonly Used Character Data Types

Data Type Description Common Use Cases

VARCHAR(n) Variable-length character Email addresses, usernames, product

string with limit. Stores up to n names, titles, descriptions with length
characters. limits, phone numbers, URLs

TEXT Variable unlimited length Article content, comments, blog posts,

character string product descriptions, JSON strings, long
notes, HTML content

CHAR(n) Fixed-length character string, Country codes (CHAR(2)), status codes,

blank-padded to n characters fixed-length identifiers, ISO codes, state
abbreviations

Note on VARCHAR vs TEXT

In PostgreSQL, VARCHAR without length limit and TEXT have identical
performance. Use VARCHAR(n) when you need length validation, TEXT for
unlimited/unpredictable length.

5
Part 1: PostgreSQL Data Types

1.3 Date/Time Types (Most Used)

Table 3 Most Commonly Used Date/Time Data Types

Data Type Description Common Use Cases

TIMESTAMP Date and time without Event logging, creation timestamps,

timezone. Format: YYYY-MM-DD modification timestamps, scheduled
HH:MM:SS events, audit trails

TIMESTAMPTZ Date and time with timezone. User-facing timestamps, international

Stores in UTC, displays in session applications, event scheduling across
timezone. timezones, server logs

DATE Calendar date without time. Birth dates, hire dates, expiration dates,
Format: YYYY-MM-DD anniversaries, due dates, publication
dates

TIME Time of day without date. Opening hours, business hours, daily
Format: HH:MM:SS schedules, recurring time patterns

INTERVAL Time span/period. Can represent Duration calculations, subscription

years, months, days, hours, periods, age calculations, time
minutes, seconds differences, recurring intervals

Recommendation
Prefer TIMESTAMPTZ over TIMESTAMP for most applications. It handles timezone
conversions automatically and prevents ambiguity.

1.4 Boolean Type (Most Used)

Table 4 Boolean Data Type

Data Type Description Common Use Cases

6
Part 1: PostgreSQL Data Types

BOOLEAN Logical boolean Active/inactive status, is_verified flag, email subscription,

or BOOL type. Values: TRUE, terms acceptance, feature toggles, is_deleted (soft delete),
FALSE, NULL is_premium, notification preferences

Valid Boolean Inputs

TRUE values: TRUE , 't' , 'true' , 'y' , 'yes' , 'on' , '1'
FALSE values: FALSE , 'f' , 'false' , 'n' , 'no' , 'off' , '0'

7
Part 1: PostgreSQL Data Types

1.5 Other Numeric Types

Table 5 Additional Numeric Data Types

Data Type Description Use Cases

SMALLINT Signed 2-byte integer. Small counters, status codes, port numbers,
Range: -32,768 to +32,767 priority levels, small lookup values

SMALLSERIAL Auto-incrementing 2-byte Small lookup tables, category IDs with

integer limited values, enum-like auto-increment

MONEY Currency amount with Simple currency storage (locale-dependent

fixed fractional precision formatting). Note: Deprecated in favor of
NUMERIC

1.6 Binary Data Types

Table 6 Binary Data Types

Data Description Use Cases

Type

BYTEA Variable-length binary string. Image data, file attachments, PDF documents,
Stores raw binary data. encrypted data, serialized objects, audio/video
files

1.7 Geometric Types

Table 7 Geometric Data Types

Data Description Use Cases

Type

POINT Geometric point (x, y Map coordinates, position markers, scatter plot
coordinates) data points

8
Part 1: PostgreSQL Data Types

LINE Infinite line defined by equation Mathematical line representations,

boundary definitions

LSEG Line segment (finite line with two Drawing elements, path segments,
endpoints) connection lines

BOX Rectangular box defined by two Bounding boxes, rectangular regions, UI

corner points element bounds

PATH Open or closed geometric path SVG paths, custom shapes, route tracking,
(list of points) drawing paths

POLYGON Closed geometric path (similar to Geographic boundaries, property

closed path) boundaries, custom shapes

CIRCLE Circle defined by center point Radial search areas, coverage zones,
and radius circular regions

1.8 Network Address Types

Table 8 Network Address Data Types

Data Description Use Cases

Type

INET IPv4 or IPv6 host address with IP address logging, user IP tracking,
optional subnet mask whitelist/blacklist storage, access logs

CIDR IPv4 or IPv6 network address Network subnet definitions, IP range storage,
(network only, not host) network topology, routing tables

MACADDR MAC address (EUI-48 format) Device identification, network hardware

tracking, DHCP lease management

MACADDR8 MAC address (EUI-64 format) Modern network hardware with EUI-64
addresses, IPv6-related MAC storage

9
Part 1: PostgreSQL Data Types

1.9 Text Search Types

Table 9 Text Search Data Types

Data Description Use Cases

Type

TSVECTOR Optimized document Preprocessed search documents, indexed

representation for full-text content for fast text search, article search
search index

TSQUERY Full-text search query Search query storage, complex search

representation expressions, boolean text queries

1.10 UUID Type

Table 10 UUID Data Type

Data Description Use Cases

Type

UUID Universally Unique Distributed system IDs, public API identifiers, session
Identifier. 128-bit value. tokens, secure random IDs, merge-safe identifiers
across databases

UUID Generation
PostgreSQL provides functions: gen_random_uuid() (recommended),
uuid_generate_v4() (requires uuid-ossp extension)

10
Part 1: PostgreSQL Data Types

1.11 XML & JSON Types

Table 11 XML and JSON Data Types

Data Description Use Cases

Type

JSON Stores JSON data as plain text Simple JSON storage when query performance
(validates syntax only) is not critical, external API responses

JSONB Binary JSON, stored in Flexible schema storage, nested data

decomposed binary format structures, configuration data, NoSQL-like
(recommended) documents, semi-structured data

XML XML data type with validation Legacy XML data, SOAP services,
support configuration files, document storage

JSON vs JSONB
Always prefer JSONB over JSON . JSONB is faster to process, supports indexing,
and eliminates duplicate keys. Use JSON only if you need to preserve exact
formatting (whitespace, key order).

1.12 Array Type

Table 12 Array Data Type

Data Description Use Cases

Type

ARRAY Variable-length Tag lists, multiple phone numbers, permission

multidimensional arrays of any flags, category assignments, coordinates,
base type matrix data

Array Syntax Examples

11
Part 1: PostgreSQL Data Types

INTEGER[] - Array of integers

VARCHAR(100)[] - Array of strings
TEXT[][] - Two-dimensional text array

1.13 Range Types

Table 13 Range Data Types

Data Type Description Use Cases

INT4RANGE Range of INTEGER values Version ranges, level ranges, ID ranges

INT8RANGE Range of BIGINT values Large number ranges, timestamp-based ranges

NUMRANGE Range of NUMERIC Price ranges, measurement ranges, numeric

values intervals

TSRANGE Range of TIMESTAMP Event schedules, booking time slots, availability

values periods

TSTZRANGE Range of TIMESTAMPTZ Timezone-aware scheduling, international

values booking systems

DATERANGE Range of DATE values Date ranges, vacation periods, project timelines,
subscription periods

1.14 Other Special Types

Table 14 Other Special Data Types

Data Description Use Cases

Type

OID Object identifier, internal System catalog references, large object

PostgreSQL type references

PG_LSN PostgreSQL Log Sequence Number Replication monitoring, WAL position

tracking

12
Part 1: PostgreSQL Data Types

TXID_SNAPSHOT User-level transaction ID Transaction visibility analysis,

snapshot concurrency debugging

BIT(n) Fixed-length bit string Flags, binary masks, permission bits

BIT VARYING(n) Variable-length bit string Variable binary flags, compressed bit
data

COMPOSITE TYPES User-defined types with Complex data structures, nested

multiple attributes objects, custom types

ENUM User-defined enumerated Status values, categories, fixed

type with fixed set of values options (e.g., 'active', 'inactive',
'pending')

DOMAIN User-defined data type with Custom validation rules, reusable

constraints constraints (e.g., positive integers
only)

13
Part 2: PostgreSQL Constraints

Part 2: PostgreSQL Constraints

Constraints are rules enforced on data columns to ensure data integrity and
consistency. PostgreSQL provides several types of constraints that can be defined
at the column or table level.

2.1 PRIMARY KEY

Table 15 PRIMARY KEY Constraint

Aspect Description

Purpose Uniquely identifies each record in a table. Combines UNIQUE and NOT
NULL constraints.

Characteristics Only one PRIMARY KEY per table. Automatically creates a unique index for
fast lookups.

Use Cases User ID, Order ID, Product ID - any column that uniquely identifies a row.
Essential for table relationships.

Syntax id SERIAL PRIMARY KEY or CONSTRAINT pk_name PRIMARY KEY

(column1, column2)

14
Part 2: PostgreSQL Constraints

-- Single column primary key

CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL
);

-- Composite primary key (multiple columns)

CREATE TABLE order_items (
order_id INTEGER,
product_id INTEGER,
quantity INTEGER,
PRIMARY KEY (order_id, product_id)
);

2.2 FOREIGN KEY

Table 16 FOREIGN KEY Constraint

Aspect Description

Purpose Establishes and enforces a link between data in two tables. Maintains
referential integrity.

Characteristics References a PRIMARY KEY or UNIQUE column in another table.

Prevents orphaned records.

Use Cases Linking orders to customers, products to categories, comments to posts -

any parent-child relationship.

Referential ON DELETE: CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION

Actions ON UPDATE: Same options

15
Part 2: PostgreSQL Constraints

-- Basic foreign key

CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(user_id),
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- With referential actions

CREATE TABLE order_items (
item_id SERIAL PRIMARY KEY,
order_id INTEGER REFERENCES orders(order_id) ON DELETE CASCADE,
product_id INTEGER REFERENCES products(product_id) ON DELETE SET NULL
);

Referential Actions Explained

CASCADE: Delete/update child records automatically
SET NULL: Set foreign key to NULL when parent is deleted
RESTRICT: Prevent deletion of parent if children exist
NO ACTION: Similar to RESTRICT but checked at end of transaction

2.3 UNIQUE
Table 17 UNIQUE Constraint

Aspect Description

Purpose Ensures all values in a column (or combination of columns) are different.

Characteristics Allows NULL values (multiple NULLs allowed). Creates unique index
automatically.

Use Cases Email addresses, usernames, phone numbers, SKU codes, social security
numbers - any value that must be unique but isn't the primary identifier.

Syntax email VARCHAR(100) UNIQUE or CONSTRAINT uq_email UNIQUE

(email)

16
Part 2: PostgreSQL Constraints

-- Single column unique

CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
email VARCHAR(100) UNIQUE NOT NULL,
username VARCHAR(50) UNIQUE
);

-- Multi-column unique (combination must be unique)

CREATE TABLE course_enrollments (
student_id INTEGER REFERENCES students(student_id),
course_id INTEGER REFERENCES courses(course_id),
semester VARCHAR(20),
UNIQUE (student_id, course_id, semester)
);

2.4 NOT NULL

Table 18 NOT NULL Constraint

Aspect Description

Purpose Ensures a column cannot have NULL values. The column must always
contain a value.

Characteristics Most fundamental constraint. Applied at column level only.

Use Cases Required fields: username, password, name, email, created_at, status - any
data that must be present.

Syntax username VARCHAR(50) NOT NULL

CREATE TABLE products (

product_id SERIAL PRIMARY KEY,
name VARCHAR(200) NOT NULL,
price NUMERIC(10,2) NOT NULL,
description TEXT, -- Can be NULL
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

17
Part 2: PostgreSQL Constraints

18
Part 2: PostgreSQL Constraints

2.5 CHECK
Table 19 CHECK Constraint

Aspect Description

Purpose Ensures all values in a column satisfy a specific condition or Boolean

expression.

Characteristics Can reference multiple columns. Evaluated on INSERT and UPDATE

operations.

Use Cases Positive prices only, age >= 18, valid email format, quantity > 0, status in
allowed values.

Syntax CHECK (condition) or CONSTRAINT chk_name CHECK

(condition)

-- Column-level check
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
name VARCHAR(200) NOT NULL,
price NUMERIC(10,2) CHECK (price > 0),
quantity INTEGER CHECK (quantity >= 0),
discount_percent INTEGER CHECK (discount_percent BETWEEN 0 AND 100)
);

-- Table-level check with multiple columns

CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
hire_date DATE NOT NULL,
termination_date DATE,
CONSTRAINT chk_dates CHECK (termination_date IS NULL OR termination_date >
hire_date)
);

19
Part 2: PostgreSQL Constraints

Common CHECK Conditions

CHECK (age >= 18) - Minimum value
CHECK (email LIKE '%@%') - Pattern matching
CHECK (status IN ('active', 'inactive', 'pending')) - Allowed values
CHECK (end_date > start_date) - Date validation

2.6 DEFAULT
Table 20 DEFAULT Constraint

Aspect Description

Purpose Sets a default value for a column when no value is specified during INSERT.

Characteristics Can be a literal value, expression, or function. Applied only on INSERT (not
UPDATE).

Use Cases Current timestamp for created_at, 'active' for status, 0 for counters, UUID
generation, default country code.

Syntax column_name TYPE DEFAULT value

CREATE TABLE users (

user_id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
status VARCHAR(20) DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
login_count INTEGER DEFAULT 0,
is_verified BOOLEAN DEFAULT FALSE,
uuid UUID DEFAULT gen_random_uuid()
);

Common DEFAULT Values

20
Part 2: PostgreSQL Constraints

CURRENT_TIMESTAMP - Current date and time

CURRENT_DATE - Current date only
gen_random_uuid() - Random UUID
nextval('sequence_name') - Next sequence value
USER - Current database user

2.7 EXCLUDE
Table 21 EXCLUDE Constraint

Aspect Description

Purpose Ensures that if any two rows are compared on the specified columns using
the specified operators, not all comparisons will return TRUE.

Characteristics Advanced constraint using GiST or SP-GiST indexes. Prevents overlapping

ranges, conflicting schedules.

Use Cases Preventing overlapping date ranges for room bookings, non-overlapping IP
ranges, exclusive time slots.

Syntax EXCLUDE USING gist (column WITH operator)

21
Part 2: PostgreSQL Constraints

-- Prevent overlapping room bookings

CREATE TABLE room_bookings (
booking_id SERIAL PRIMARY KEY,
room_id INTEGER NOT NULL,
booking_period TSTZRANGE NOT NULL,
booked_by VARCHAR(100),
EXCLUDE USING gist (
room_id WITH =,
booking_period WITH &&
)
);

-- Prevent overlapping IP ranges

CREATE TABLE ip_allocations (
allocation_id SERIAL PRIMARY KEY,
network CIDR NOT NULL,
EXCLUDE USING gist (network WITH &&)
);

EXCLUDE Operators
&& - Overlaps (for ranges)
= - Equals
<> - Not equals
Requires btree_gist extension for scalar types: CREATE EXTENSION btree_gist;

22
Part 2: PostgreSQL Constraints

Quick Reference Summary

Most Used Data Types Quick Pick

Category Recommended Type When to Use

Primary Keys SERIAL or BIGSERIAL Auto-incrementing IDs

Money/Prices NUMERIC(10,2) Exact decimal calculations

Strings (limited) VARCHAR(n) Emails, names, titles

Strings (unlimited) TEXT Descriptions, content

Timestamps TIMESTAMPTZ Event logging, created_at

Dates only DATE Birth dates, anniversaries

Flags BOOLEAN Status flags, is_active

JSON data JSONB Flexible schema storage

Constraints Priority

Priority Constraint Essential For

1 (Must) PRIMARY KEY Every table needs one

2 (Must) NOT NULL Required fields

3 (Should) FOREIGN KEY Table relationships

4 (Should) UNIQUE Alternate identifiers

5 (Should) CHECK Data validation

6 (Nice) DEFAULT Auto-populate fields

23