PostgreSQL Data Types

Introduction

PostgreSQL provides a comprehensive set of data types for storing and manipulating various kinds of data. Understanding these data types is crucial for designing efficient database schemas and writing correct SQL queries.

Data Type Categories

PostgreSQL data types are organized into several categories:

Numeric Types - Integers, floating-point, exact numerics
Character Types - Strings, text
Date/Time Types - Dates, times, timestamps
Boolean Type - True/false values
Binary Data Types - Binary strings
JSON Types - Structured data
UUID Type - Universal unique identifiers
Array Types - Arrays of elements
Enumerated Types - Custom enumerated values
Geometric Types - Points, lines, shapes
Network Address Types - IP addresses, MAC addresses
Range Types - Range of values

Numeric Types

Integer Types

Type	Storage	Range	Description
SMALLINT	2 bytes	-32,768 to 32,767	Small integer
INTEGER (INT)	4 bytes	-2,147,483,648 to 2,147,483,647	Standard integer
BIGINT	8 bytes	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807	Large integer

SMALLINT Example

sql

CREATE TABLE products (
    id SMALLSERIAL PRIMARY KEY,
    quantity SMALLINT NOT NULL,
    min_stock SMALLINT DEFAULT 10
);

INSERT INTO products (quantity) VALUES (100), (500), (32767);

INTEGER Example

sql

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    post_count INTEGER DEFAULT 0,
    followers INTEGER DEFAULT 0
);

INSERT INTO users (post_count, followers) VALUES 
    (50, 1500),
    (1000000, 5000000);

BIGINT Example

sql

CREATE TABLE statistics (
    id BIGSERIAL PRIMARY KEY,
    population BIGINT,
    national_debt BIGINT,
    page_views BIGINT
);

INSERT INTO statistics (population, national_debt, page_views) VALUES
    (330000000, 28000000000000, 999999999999999);

Serial Types (Auto-increment)

Serial types are PostgreSQL-specific auto-increment types:

Type	Storage	Range	Equivalent To
SMALLSERIAL	2 bytes	1 to 32,767	SMALLINT with sequence
SERIAL	4 bytes	1 to 2,147,483,647	INTEGER with sequence
BIGSERIAL	8 bytes	1 to 9,223,372,036,854,775,807	BIGINT with sequence

sql

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_number VARCHAR(20) UNIQUE NOT NULL
);

-- SERIAL creates a sequence automatically
-- Get next value
SELECT nextval('orders_order_id_seq');

-- Set sequence to specific value
SELECT setval('orders_order_id_seq', 1000);

-- Get current value
SELECT currval('orders_order_id_seq');

Floating-Point Types

Type	Storage	Precision	Range
REAL	4 bytes	6 decimal digits	1E-37 to 1E+37
DOUBLE PRECISION	8 bytes	15 decimal digits	1E-307 to 1E+308

sql

CREATE TABLE measurements (
    id SERIAL PRIMARY KEY,
    temperature REAL,
    latitude DOUBLE PRECISION,
    longitude DOUBLE PRECISION,
    pi_value DOUBLE PRECISION DEFAULT 3.141592653589793
);

INSERT INTO measurements (temperature, latitude, longitude) VALUES
    (98.6, 37.7749, -122.4194),
    (-12.5, 40.7128, -74.0060);

Exact Numeric Types

NUMERIC and DECIMAL are exact numeric types with user-specified precision.

sql

-- NUMERIC(precision, scale)
-- precision: total number of digits
-- scale: number of digits after decimal point

CREATE TABLE financial (
    id SERIAL PRIMARY KEY,
    price NUMERIC(10, 2),           -- 10 digits total, 2 after decimal
    quantity NUMERIC(5),            -- 5 digits, no decimal
    exchange_rate NUMERIC(10, 6),   -- 10 digits, 6 after decimal
    balance DECIMAL(15, 2)          -- DECIMAL is alias for NUMERIC
);

INSERT INTO financial (price, quantity, exchange_rate, balance) VALUES
    (19.99, 100, 1.234567, 10000.50),
    (999.99, 5, 0.123456, 50000.00);

-- Precision examples
SELECT 
    NUMERIC '123.45' AS exact_number,
    NUMERIC(10, 2) '123.4567' AS rounded,  -- Becomes 123.46
    NUMERIC '9999999999' AS large_number;

Money Type

The MONEY type stores currency amounts with fixed fractional precision.

sql

CREATE TABLE transactions (
    id SERIAL PRIMARY KEY,
    amount MONEY,
    balance MONEY
);

INSERT INTO transactions (amount, balance) VALUES
    ('$19.99', '$1,234.56'),
    (19.99, 1234.56);

SELECT 
    amount,
    balance,
    amount + balance AS total
FROM transactions;

Note: MONEY type is locale-dependent. For international applications, use NUMERIC instead.

Character Types

Type	Description	Storage
CHAR(n)	Fixed-length character string	n bytes
VARCHAR(n)	Variable-length with limit	Actual length + 1 byte
TEXT	Variable unlimited length	Actual length + 1 byte

CHAR Example

sql

CREATE TABLE codes (
    country_code CHAR(2),      -- Always 2 characters
    state_code CHAR(2),
    zip_code CHAR(5)
);

INSERT INTO codes VALUES ('US', 'CA', '94102');
-- 'US' is stored as 'US' (no padding visible in output)

VARCHAR Example

sql

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    bio VARCHAR(500)
);

INSERT INTO users (username, email, bio) VALUES
    ('john_doe', 'john@example.com', 'Software developer'),
    ('jane_smith', 'jane@example.com', NULL);

TEXT Example

sql

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    summary TEXT
);

INSERT INTO articles (title, content) VALUES
    ('PostgreSQL Guide', 'This is a very long article content...');

Best Practice: Use TEXT for variable-length strings unless you need to enforce a maximum length.

Boolean Type

The BOOLEAN type stores true/false values.

sql

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    is_verified BOOLEAN DEFAULT FALSE,
    has_premium BOOLEAN
);

-- Various ways to insert boolean values
INSERT INTO users (username, is_active, is_verified, has_premium) VALUES
    ('user1', TRUE, FALSE, NULL),
    ('user2', 't', 'f', 'yes'),
    ('user3', 'true', 'false', 'no'),
    ('user4', '1', '0', 'y');

-- Query boolean values
SELECT * FROM users WHERE is_active = TRUE;
SELECT * FROM users WHERE is_verified;  -- Same as is_verified = TRUE
SELECT * FROM users WHERE NOT is_active;

Boolean representations:

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

Date/Time Types

Type	Storage	Description	Range
DATE	4 bytes	Date only	4713 BC to 5874897 AD
TIME	8 bytes	Time of day	00:00:00 to 24:00:00
TIMESTAMP	8 bytes	Date and time	4713 BC to 294276 AD
TIMESTAMPTZ	8 bytes	Timestamp with timezone	4713 BC to 294276 AD
INTERVAL	16 bytes	Time interval	-178000000 years to 178000000 years

DATE Example

sql

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name VARCHAR(100),
    event_date DATE NOT NULL,
    created_at DATE DEFAULT CURRENT_DATE
);

INSERT INTO events (event_name, event_date) VALUES
    ('Conference', '2024-06-15'),
    ('Workshop', DATE '2024-07-20');

-- Date operations
SELECT 
    event_date,
    event_date + INTERVAL '7 days' AS one_week_later,
    CURRENT_DATE - event_date AS days_ago
FROM events;

TIME Example

sql

CREATE TABLE schedules (
    id SERIAL PRIMARY KEY,
    task_name VARCHAR(100),
    start_time TIME NOT NULL,
    end_time TIME NOT NULL
);

INSERT INTO schedules (task_name, start_time, end_time) VALUES
    ('Morning Meeting', '09:00:00', '10:00:00'),
    ('Lunch Break', TIME '12:00', TIME '13:00');

TIMESTAMP Example

sql

CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP
);

INSERT INTO logs (message) VALUES ('System started');

-- Timestamp operations
SELECT 
    created_at,
    created_at + INTERVAL '1 hour' AS one_hour_later,
    EXTRACT(YEAR FROM created_at) AS year,
    EXTRACT(MONTH FROM created_at) AS month,
    EXTRACT(DAY FROM created_at) AS day
FROM logs;

TIMESTAMPTZ Example

sql

CREATE TABLE user_actions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    action VARCHAR(50),
    action_time TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO user_actions (user_id, action) VALUES (1, 'login');

-- Timezone operations
SELECT 
    action_time,
    action_time AT TIME ZONE 'UTC' AS utc_time,
    action_time AT TIME ZONE 'America/New_York' AS ny_time
FROM user_actions;

Best Practice: Always use TIMESTAMPTZ for storing timestamps to handle timezone conversions properly.

INTERVAL Example

sql

SELECT 
    INTERVAL '1 day' AS one_day,
    INTERVAL '2 hours 30 minutes' AS duration,
    INTERVAL '1 year 2 months 3 days' AS period,
    NOW() + INTERVAL '7 days' AS next_week;

-- Interval calculations
CREATE TABLE subscriptions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    start_date TIMESTAMP,
    duration INTERVAL,
    end_date TIMESTAMP GENERATED ALWAYS AS (start_date + duration) STORED
);

INSERT INTO subscriptions (user_id, start_date, duration) VALUES
    (1, NOW(), INTERVAL '1 month'),
    (2, NOW(), INTERVAL '1 year');

Binary Data Types

Type	Storage	Description
BYTEA	Variable	Binary data (byte array)

sql

CREATE TABLE files (
    id SERIAL PRIMARY KEY,
    filename VARCHAR(255),
    file_data BYTEA,
    file_size INTEGER
);

-- Insert binary data
INSERT INTO files (filename, file_data) VALUES
    ('image.png', '\xDEADBEEF'::bytea),
    ('document.pdf', decode('48656C6C6F', 'hex'));

-- Query binary data
SELECT 
    filename,
    encode(file_data, 'hex') AS hex_data,
    encode(file_data, 'base64') AS base64_data,
    octet_length(file_data) AS size_bytes
FROM files;

JSON Types

PostgreSQL supports two JSON data types: JSON and JSONB (binary JSON).

Type	Storage	Description	Indexing
JSON	Variable	Text-based JSON	No
JSONB	Variable	Binary JSON	Yes (GIN index)

Recommendation: Use JSONB for better performance and indexing capabilities.

JSON Example

sql

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    attributes JSON
);

INSERT INTO products (name, attributes) VALUES
    ('Laptop', '{"brand": "Dell", "ram": "16GB", "storage": "512GB"}'),
    ('Phone', '{"brand": "Apple", "model": "iPhone 14", "color": "black"}');

-- Query JSON data
SELECT 
    name,
    attributes->>'brand' AS brand,
    attributes->>'ram' AS ram
FROM products;

JSONB Example

sql

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    profile JSONB
);

INSERT INTO users (username, profile) VALUES
    ('john', '{"age": 30, "city": "New York", "hobbies": ["reading", "gaming"]}'),
    ('jane', '{"age": 25, "city": "London", "hobbies": ["music", "travel"]}');

-- JSONB queries
SELECT * FROM users WHERE profile->>'city' = 'New York';
SELECT * FROM users WHERE profile @> '{"age": 30}';
SELECT * FROM users WHERE profile ? 'hobbies';

-- Create GIN index for better performance
CREATE INDEX idx_users_profile ON users USING GIN (profile);

-- Update JSONB
UPDATE users 
SET profile = profile || '{"verified": true}'
WHERE username = 'john';

JSONB Operators

sql

-- -> Get JSON object field
SELECT profile->'hobbies' FROM users;

-- ->> Get JSON object field as text
SELECT profile->>'city' FROM users;

-- @> Contains
SELECT * FROM users WHERE profile @> '{"city": "London"}';

-- ? Key exists
SELECT * FROM users WHERE profile ? 'age';

-- || Concatenate
UPDATE users SET profile = profile || '{"premium": true}';

UUID Type

UUID (Universally Unique Identifier) is a 128-bit identifier.

sql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE sessions (
    session_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id INTEGER,
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO sessions (user_id) VALUES (1), (2);

SELECT * FROM sessions;
-- Output: session_id like '550e8400-e29b-41d4-a716-446655440000'

Array Types

PostgreSQL allows columns to be defined as arrays of any built-in or user-defined type.

sql

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    tags TEXT[],
    prices NUMERIC(10,2)[],
    dimensions INTEGER[]
);

-- Insert arrays
INSERT INTO products (name, tags, prices, dimensions) VALUES
    ('Laptop', ARRAY['electronics', 'computers'], ARRAY[999.99, 1299.99], ARRAY[15, 10, 1]),
    ('Book', '{"fiction", "bestseller"}', '{9.99, 12.99}', '{8, 6, 1}');

-- Query arrays
SELECT * FROM products WHERE 'electronics' = ANY(tags);
SELECT * FROM products WHERE tags @> ARRAY['computers'];

-- Array functions
SELECT 
    name,
    array_length(tags, 1) AS tag_count,
    tags[1] AS first_tag,
    array_append(tags, 'new') AS updated_tags
FROM products;

Enumerated Types

Enumerated types are custom data types with a static, ordered set of values.

sql

-- Create enum type
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_number VARCHAR(20),
    status order_status DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO orders (order_number, status) VALUES
    ('ORD-001', 'pending'),
    ('ORD-002', 'processing'),
    ('ORD-003', 'shipped');

-- Query with enum
SELECT * FROM orders WHERE status = 'pending';
SELECT * FROM orders WHERE status > 'pending';  -- Ordered comparison

-- List all enum values
SELECT enum_range(NULL::order_status);

Geometric Types

PostgreSQL supports a variety of geometric types for 2D spatial data.

Geometric data types represent two-dimensional objects on a plane.

The table below lists the geometric types supported by PostgreSQL.

The most fundamental type is the point, which acts as the basis for all other types.

Name	Storage Size	Description	Representation
point	16 bytes	Point on a plane	(x,y)
line	32 bytes	Infinite line (not fully implemented)	((x1,y1),(x2,y2))
lseg	32 bytes	Finite line segment	((x1,y1),(x2,y2))
box	32 bytes	Rectangular box	((x1,y1),(x2,y2))
path	16+16n bytes	Closed path (similar to a polygon)	((x1,y1),...)
path	16+16n bytes	Open path	[(x1,y1),...]
polygon	40+16n bytes	Polygon (similar to a closed path)	((x1,y1),...)
circle	24 bytes	Circle (center point and radius)	<(x,y),r>

sql

CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    position POINT,
    area BOX,
    coverage CIRCLE
);

INSERT INTO locations (name, position, area, coverage) VALUES
    ('Store A', POINT(10, 20), BOX(POINT(0,0), POINT(100,100)), CIRCLE(POINT(50,50), 25)),
    ('Store B', '(30, 40)', '((0,0),(50,50))', '<(25,25),15>');

-- Geometric operations
SELECT 
    name,
    position,
    position <-> POINT(0,0) AS distance_from_origin,
    area @> POINT(50,50) AS contains_point
FROM locations;

Network Address Types

PostgreSQL provides types for storing network addresses.

Type	Storage	Description
CIDR	7 or 19 bytes	IPv4/IPv6 network
INET	7 or 19 bytes	IPv4/IPv6 host and network
MACADDR	6 bytes	MAC address
MACADDR8	8 bytes	MAC address (EUI-64 format)

sql

CREATE TABLE network_devices (
    id SERIAL PRIMARY KEY,
    device_name VARCHAR(100),
    ip_address INET,
    network CIDR,
    mac_address MACADDR
);

INSERT INTO network_devices (device_name, ip_address, network, mac_address) VALUES
    ('Server 1', '192.168.1.100', '192.168.1.0/24', '08:00:2b:01:02:03'),
    ('Router', '10.0.0.1', '10.0.0.0/8', '08-00-2b-01-02-03');

-- Network operations
SELECT 
    device_name,
    ip_address,
    host(ip_address) AS host_only,
    network,
    broadcast(network) AS broadcast_address,
    netmask(network) AS subnet_mask
FROM network_devices;

-- Check if IP is in network
SELECT * FROM network_devices WHERE ip_address << CIDR '192.168.1.0/24';

Range Types

Range types represent a range of values of some element type.

Type	Description
INT4RANGE	Range of INTEGER
INT8RANGE	Range of BIGINT
NUMRANGE	Range of NUMERIC
TSRANGE	Range of TIMESTAMP
TSTZRANGE	Range of TIMESTAMPTZ
DATERANGE	Range of DATE

sql

CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_id INTEGER,
    guest_name VARCHAR(100),
    stay_period DATERANGE,
    price_range NUMRANGE
);

INSERT INTO reservations (room_id, guest_name, stay_period, price_range) VALUES
    (101, 'John Doe', '[2024-06-01,2024-06-05)', '[100,150)'),
    (102, 'Jane Smith', DATERANGE('2024-06-10', '2024-06-15'), NUMRANGE(150, 200));

-- Range operations
SELECT * FROM reservations WHERE stay_period @> DATE '2024-06-03';
SELECT * FROM reservations WHERE stay_period && DATERANGE('2024-06-01', '2024-06-10');

-- Range functions
SELECT 
    guest_name,
    lower(stay_period) AS check_in,
    upper(stay_period) AS check_out,
    upper(stay_period) - lower(stay_period) AS nights
FROM reservations;

XML Type

PostgreSQL supports XML data type for storing XML data.

sql

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100),
    content XML
);

INSERT INTO documents (title, content) VALUES
    ('Sample', '<doc><title>Hello</title><body>World</body></doc>');

-- Query XML
SELECT 
    title,
    xpath('/doc/title/text()', content) AS xml_title
FROM documents;

Composite Types

Composite types represent the structure of a row or record.

sql

-- Create composite type
CREATE TYPE address AS (
    street VARCHAR(100),
    city VARCHAR(50),
    state CHAR(2),
    zip_code VARCHAR(10)
);

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    home_address address,
    work_address address
);

INSERT INTO customers (name, home_address, work_address) VALUES
    ('John Doe', 
     ROW('123 Main St', 'New York', 'NY', '10001'),
     ROW('456 Work Ave', 'New York', 'NY', '10002'));

-- Query composite type
SELECT 
    name,
    (home_address).city AS home_city,
    (work_address).city AS work_city
FROM customers;

Domain Types

Domains are user-defined data types based on existing types with optional constraints.

sql

-- Create domain with constraints
CREATE DOMAIN email AS VARCHAR(255)
    CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$');

CREATE DOMAIN positive_integer AS INTEGER
    CHECK (VALUE > 0);

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

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    email_address email,
    age positive_integer,
    zip us_postal_code
);

-- Valid insert
INSERT INTO users (username, email_address, age, zip) VALUES
    ('john_doe', 'john@example.com', 30, '12345');

-- Invalid insert (will fail)
-- INSERT INTO users (username, email_address, age) VALUES
--     ('jane', 'invalid-email', -5);

Choosing the Right Data Type

Best Practices

Use appropriate size: Don't use BIGINT when INTEGER suffices
Prefer TIMESTAMPTZ: Always store timestamps with timezone
Use TEXT over VARCHAR: Unless you need length constraint
JSONB over JSON: For better performance and indexing
NUMERIC for money: Avoid MONEY type for international apps
Use domains: For reusable constraints
Consider indexes: Some types support specific index types

Performance Considerations

sql

-- Good: Appropriate types
CREATE TABLE optimized (
    id INTEGER PRIMARY KEY,
    status SMALLINT,
    price NUMERIC(10,2),
    created_at TIMESTAMPTZ
);

-- Bad: Oversized types
CREATE TABLE wasteful (
    id BIGINT PRIMARY KEY,
    status BIGINT,
    price DOUBLE PRECISION,
    created_at VARCHAR(50)
);

Data Type Conversion

PostgreSQL provides several ways to convert between data types.

CAST Function

sql

SELECT 
    CAST('123' AS INTEGER) AS int_value,
    CAST('2024-01-01' AS DATE) AS date_value,
    CAST(123.45 AS INTEGER) AS truncated,
    CAST('true' AS BOOLEAN) AS bool_value;

:: Operator (PostgreSQL-specific)

sql

SELECT 
    '123'::INTEGER AS int_value,
    '2024-01-01'::DATE AS date_value,
    123.45::INTEGER AS truncated,
    'true'::BOOLEAN AS bool_value;

Conversion Functions

sql

-- String to number
SELECT 
    to_number('1,234.56', '9,999.99') AS number_value,
    to_date('2024-01-01', 'YYYY-MM-DD') AS date_value,
    to_timestamp('2024-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS') AS timestamp_value;

-- Number to string
SELECT 
    to_char(1234.56, '9,999.99') AS formatted_number,
    to_char(NOW(), 'YYYY-MM-DD HH24:MI:SS') AS formatted_date;

Choosing the Right Data Type

Best Practices

Use appropriate numeric types
- Use INTEGER for most whole numbers
- Use BIGINT only when needed (saves space)
- Use NUMERIC for exact decimal calculations (money, measurements)
- Avoid REAL/DOUBLE PRECISION for financial data
String types
- Use TEXT for variable-length strings without limit
- Use VARCHAR(n) only when you need to enforce a maximum length
- Avoid CHAR(n) unless you need fixed-length strings
Date/Time types
- Always use TIMESTAMPTZ for timestamps (handles timezones)
- Use DATE for dates without time
- Use INTERVAL for durations
Boolean vs Integer
- Use BOOLEAN for true/false values (clearer intent)
- Don't use INTEGER (0/1) for boolean logic
JSON types
- Prefer JSONB over JSON (better performance, indexing)
- Use JSONB for semi-structured data
Arrays vs separate tables
- Use arrays for simple lists that don't need querying
- Use separate tables for complex relationships

Performance Considerations

sql

-- Good: Appropriate types
CREATE TABLE users (
    id SERIAL PRIMARY KEY,              -- Auto-increment
    username VARCHAR(50) NOT NULL,      -- Limited length
    email VARCHAR(255) NOT NULL,        -- Standard email length
    age SMALLINT,                       -- Small numbers
    balance NUMERIC(10,2),              -- Exact decimal
    is_active BOOLEAN DEFAULT TRUE,     -- Clear boolean
    created_at TIMESTAMPTZ DEFAULT NOW() -- Timezone-aware
);

-- Avoid: Inappropriate types
CREATE TABLE users_bad (
    id BIGINT,                          -- Unnecessarily large
    username TEXT,                      -- No length limit
    email TEXT,                         -- No validation
    age INTEGER,                        -- Too large for age
    balance REAL,                       -- Imprecise for money
    is_active INTEGER,                  -- Unclear (0/1?)
    created_at TIMESTAMP                -- No timezone
);

Common Data Type Mistakes

Mistake 1: Using VARCHAR without length

sql

-- Unclear intent
CREATE TABLE products (name VARCHAR);

-- Better: Specify length or use TEXT
CREATE TABLE products (name VARCHAR(200));
CREATE TABLE products (name TEXT);

Mistake 2: Using REAL for money

sql

-- Wrong: Precision issues
CREATE TABLE orders (total REAL);

-- Correct: Use NUMERIC
CREATE TABLE orders (total NUMERIC(10,2));

Mistake 3: Not using SERIAL for auto-increment

sql

-- Manual: More work
CREATE SEQUENCE user_id_seq;
CREATE TABLE users (
    id INTEGER DEFAULT nextval('user_id_seq')
);

-- Better: Use SERIAL
CREATE TABLE users (
    id SERIAL PRIMARY KEY
);

Mistake 4: Storing dates as strings

sql

-- Wrong: String dates
CREATE TABLE events (event_date VARCHAR(10));
INSERT INTO events VALUES ('2024-01-01');

-- Correct: Use DATE type
CREATE TABLE events (event_date DATE);
INSERT INTO events VALUES ('2024-01-01');

Mistake 5: Not using TIMESTAMPTZ

sql

-- Wrong: No timezone
CREATE TABLE logs (created_at TIMESTAMP);

-- Correct: With timezone
CREATE TABLE logs (created_at TIMESTAMPTZ);

Data Type Size Comparison

Type	Storage	Range/Precision
BOOLEAN	1 byte	true/false
SMALLINT	2 bytes	-32,768 to 32,767
INTEGER	4 bytes	-2 billion to 2 billion
BIGINT	8 bytes	-9 quintillion to 9 quintillion
REAL	4 bytes	6 decimal digits precision
DOUBLE PRECISION	8 bytes	15 decimal digits precision
NUMERIC	Variable	User-specified precision
CHAR(n)	n bytes	Fixed length
VARCHAR(n)	Variable	Up to n characters
TEXT	Variable	Unlimited length
DATE	4 bytes	Date only
TIME	8 bytes	Time only
TIMESTAMP	8 bytes	Date and time
TIMESTAMPTZ	8 bytes	Date, time, and timezone
UUID	16 bytes	Unique identifier
JSON	Variable	Text-based JSON
JSONB	Variable	Binary JSON

Summary

PostgreSQL offers a rich set of data types:

Numeric types: INTEGER, BIGINT, NUMERIC, REAL, DOUBLE PRECISION
Character types: CHAR, VARCHAR, TEXT
Date/Time types: DATE, TIME, TIMESTAMP, TIMESTAMPTZ, INTERVAL
Boolean type: TRUE/FALSE values
Binary type: BYTEA for binary data
JSON types: JSON and JSONB for structured data
UUID: Unique identifiers
Arrays: Multi-value columns
Enums: Custom enumerated types
Geometric types: Spatial data
Network types: IP and MAC addresses
Range types: Value ranges

Key Takeaways:

Choose the smallest appropriate type for your data
Use NUMERIC for exact decimal calculations
Always use TIMESTAMPTZ for timestamps
Prefer JSONB over JSON
Use TEXT instead of VARCHAR without length
Use appropriate types for better performance and data integrity

Understanding and choosing the right data types is fundamental to building efficient and reliable PostgreSQL databases.

PostgreSQL Data Types ​

Introduction ​

Data Type Categories ​

Numeric Types ​

Integer Types ​

SMALLINT Example ​

INTEGER Example ​

BIGINT Example ​

Serial Types (Auto-increment) ​

Floating-Point Types ​

Exact Numeric Types ​

Money Type ​

Character Types ​

CHAR Example ​

VARCHAR Example ​

TEXT Example ​

Boolean Type ​

Date/Time Types ​

DATE Example ​

TIME Example ​

TIMESTAMP Example ​

TIMESTAMPTZ Example ​

INTERVAL Example ​

Binary Data Types ​

JSON Types ​

JSON Example ​

JSONB Example ​

JSONB Operators ​

UUID Type ​

Array Types ​

Enumerated Types ​

Geometric Types ​

Network Address Types ​

Range Types ​

XML Type ​

Composite Types ​

Domain Types ​

Choosing the Right Data Type ​

Best Practices ​

Performance Considerations ​

Data Type Conversion ​

CAST Function ​

:: Operator (PostgreSQL-specific) ​

Conversion Functions ​

Choosing the Right Data Type ​

Best Practices ​

Performance Considerations ​

Common Data Type Mistakes ​

Mistake 1: Using VARCHAR without length ​

Mistake 2: Using REAL for money ​

Mistake 3: Not using SERIAL for auto-increment ​

Mistake 4: Storing dates as strings ​

Mistake 5: Not using TIMESTAMPTZ ​

Data Type Size Comparison ​

Summary ​

PostgreSQL Data Types

Introduction

Data Type Categories

Numeric Types

Integer Types

SMALLINT Example

INTEGER Example

BIGINT Example

Serial Types (Auto-increment)

Floating-Point Types

Exact Numeric Types

Money Type

Character Types

CHAR Example

VARCHAR Example

TEXT Example

Boolean Type

Date/Time Types

DATE Example

TIME Example

TIMESTAMP Example

TIMESTAMPTZ Example

INTERVAL Example

Binary Data Types

JSON Types

JSON Example

JSONB Example

JSONB Operators

UUID Type

Array Types

Enumerated Types

Geometric Types

Network Address Types

Range Types

XML Type

Composite Types

Domain Types

Choosing the Right Data Type

Best Practices

Performance Considerations

Data Type Conversion

CAST Function

:: Operator (PostgreSQL-specific)

Conversion Functions

Choosing the Right Data Type

Best Practices

Performance Considerations

Common Data Type Mistakes

Mistake 1: Using VARCHAR without length

Mistake 2: Using REAL for money

Mistake 3: Not using SERIAL for auto-increment

Mistake 4: Storing dates as strings

Mistake 5: Not using TIMESTAMPTZ

Data Type Size Comparison

Summary