PostgreSQL Data Types

Understanding data types for efficient database design

📊 What are Data Types?

Data types define what kind of data can be stored in a column. PostgreSQL offers rich data types including numeric, text, date/time, boolean, and specialized types for various needs. 


-- Creating table with different data types
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price NUMERIC(10, 2),
    in_stock BOOLEAN,
    created_at TIMESTAMP
);

Output: 

CREATE TABLE

Common Data Types

🔢

Numeric Types

Store numbers and calculations 

INTEGER, BIGINT, DECIMAL,
NUMERIC, REAL, SERIAL
📝

Character Types

Store text and strings 

VARCHAR(n), CHAR(n),
TEXT
📅

Date/Time Types

Store dates and timestamps 

DATE, TIME, TIMESTAMP,
INTERVAL
✅

Boolean Type

Store true/false values 

BOOLEAN
-- TRUE, FALSE, NULL

🔹 Numeric Data Types

PostgreSQL provides various numeric types for different precision and range requirements. Choose the appropriate type based on your data size and precision needs. 

-- Integer types
CREATE TABLE numbers_demo (
    small_num SMALLINT,      -- -32,768 to 32,767
    regular_num INTEGER,     -- -2 billion to 2 billion
    big_num BIGINT,          -- Very large numbers
    auto_id SERIAL           -- Auto-incrementing integer
);

-- Decimal types
CREATE TABLE prices (
    product_id SERIAL,
    price NUMERIC(10, 2),    -- 10 digits, 2 after decimal
    discount DECIMAL(5, 2),  -- Same as NUMERIC
    weight REAL              -- Floating point
);

-- Insert examples
INSERT INTO prices (price, discount, weight)
VALUES (99.99, 15.50, 2.5);

Output: 

INSERT 0 1

🔹 Character Data Types

Text data types store strings of varying lengths. VARCHAR is most commonly used for variable-length text, while TEXT is for unlimited length strings. 

-- Character types
CREATE TABLE users (
    username VARCHAR(50),     -- Variable length, max 50
    password CHAR(64),        -- Fixed length 64
    bio TEXT,                 -- Unlimited length
    status VARCHAR(20)
);

-- Insert examples
INSERT INTO users (username, password, bio, status)
VALUES (
    'john_doe',
    'hashed_password_here',
    'Software developer who loves PostgreSQL',
    'active'
);

-- Query example
SELECT username, status FROM users;

Output: 

username  | status
----------+--------
john_doe  | active

🔹 Date and Time Types

Date and time types handle temporal data with various levels of precision. Use TIMESTAMP for date and time together, DATE for just dates, and TIME for time only. 

-- Date/Time types
CREATE TABLE events (
    event_id SERIAL,
    event_date DATE,                    -- YYYY-MM-DD
    event_time TIME,                    -- HH:MM:SS
    created_at TIMESTAMP,               -- Date + Time
    updated_at TIMESTAMPTZ,             -- With timezone
    duration INTERVAL                   -- Time span
);

-- Insert examples
INSERT INTO events (event_date, event_time, created_at, duration)
VALUES (
    '2024-12-25',
    '14:30:00',
    NOW(),
    INTERVAL '2 hours'
);

-- Query with date functions
SELECT 
    event_date,
    EXTRACT(YEAR FROM event_date) as year,
    AGE(event_date) as days_ago
FROM events;

Output: 

event_date  | year | days_ago
------------+------+----------
2024-12-25  | 2024 | 10 months

🔹 Boolean Data Type

Boolean type stores true or false values, useful for flags and status indicators. PostgreSQL accepts various formats for boolean values. 

-- Boolean type
CREATE TABLE products (
    product_id SERIAL,
    name VARCHAR(100),
    in_stock BOOLEAN,
    is_featured BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE
);

-- Insert with boolean values
INSERT INTO products (name, in_stock, is_featured)
VALUES 
    ('Laptop', TRUE, FALSE),
    ('Mouse', 't', 'yes'),      -- Alternative formats
    ('Keyboard', 1, 0);         -- 1=true, 0=false

-- Query with boolean
SELECT name, in_stock
FROM products
WHERE is_active = TRUE;

Output: 

name     | in_stock
---------+----------
Laptop   | t
Mouse    | t
Keyboard | t

🔹 Special Data Types

PostgreSQL offers specialized types for specific use cases like JSON, arrays, and geometric data. These types provide powerful functionality for modern applications. 

-- JSON type
CREATE TABLE settings (
    user_id INTEGER,
    preferences JSON,
    metadata JSONB              -- Binary JSON (faster)
);

INSERT INTO settings (user_id, preferences)
VALUES (1, '{"theme": "dark", "language": "en"}');

-- Array type
CREATE TABLE tags (
    post_id INTEGER,
    tag_list TEXT[]             -- Array of text
);

INSERT INTO tags (post_id, tag_list)
VALUES (1, ARRAY['postgresql', 'database', 'sql']);

-- UUID type
CREATE TABLE sessions (
    session_id UUID DEFAULT gen_random_uuid(),
    user_id INTEGER,
    created_at TIMESTAMP DEFAULT NOW()
);

🔹 Choosing the Right Data Type

Select appropriate data types for optimal performance and storage:

  • Use SERIAL for auto-incrementing IDs
  • Use VARCHAR(n) when you know max length
  • Use TEXT for unlimited text
  • Use NUMERIC for money (exact precision)
  • Use INTEGER for whole numbers
  • Use TIMESTAMP for date and time together
  • Use BOOLEAN for yes/no flags

🧠 Test Your Knowledge

Which data type is best for storing prices?