Insert_data

Chapter 9: INSERT - Adding Data

Inserting Data into PostgreSQL Tables

---

9.1 INSERT Fundamentals

The INSERT statement adds new rows to a table. PostgreSQL provides multiple ways to insert data efficiently.

                    INSERT Statement Options
    ========================================================================

                              Start: Insert Data
                                        |
                    ┌───────────────────┼───────────────────┐
                    |                   |                   |
                    v                   v                   v
          ┌─────────────┐     ┌─────────────┐     ┌─────────────┐
          │ Single Row  │     │ Multi-Row   │     │  FROM       │
          │ INSERT      │     │ INSERT      │     │ SELECT      │
          │             │     │             │     │             │
          │ VALUES()    │     │ VALUES(),   │     │ INSERT ...  │
          │             │     │ ()          │     │ SELECT      │
          └─────────────┘     └─────────────┘     └─────────────┘
    ========================================================================

---

9.2 Basic INSERT Operations

-- Create table for examples
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    stock INTEGER DEFAULT 0,
    category VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert a single row (all columns)
INSERT INTO products (name, description, price, stock, category)
VALUES ('Laptop', 'High-performance laptop', 999.99, 50, 'Electronics');

-- Insert with column list (recommended)
INSERT INTO products (name, price, stock)
VALUES ('Mouse', 29.99, 100);

-- Let DEFAULT handle values
INSERT INTO products (name, price)
VALUES ('Keyboard', 79.99);

-- Insert multiple rows in one statement
INSERT INTO products (name, description, price, stock, category) VALUES
    ('Monitor', '27-inch 4K display', 399.99, 25, 'Electronics'),
    ('Headphones', 'Wireless noise-cancelling', 199.99, 75, 'Audio'),
    ('Webcam', '1080p HD webcam', 89.99, 40, 'Electronics');

-- Using DEFAULT values explicitly
INSERT INTO products VALUES (DEFAULT, 'Tablet', NULL, 299.99, DEFAULT, 'Electronics');

---

9.3 INSERT with DEFAULT Values

-- Using DEFAULT keyword
INSERT INTO products (name, price)
VALUES ('USB Cable', DEFAULT);  -- Uses DEFAULT for all other columns

-- Using DEFAULT in multiple columns
INSERT INTO products (name, price, stock)
VALUES ('Phone Case', 19.99, DEFAULT);

-- Check what happens with different definitions
CREATE TABLE examples (
    id SERIAL PRIMARY KEY,
    required_field VARCHAR(50) NOT NULL,
    optional_field VARCHAR(50) DEFAULT 'default_value',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP
);

-- Inserting with some defaults
INSERT INTO examples (required_field) VALUES ('Required Only');
-- optional_field = 'default_value', created_at = NOW()

INSERT INTO examples (required_field, optional_field)
VALUES ('Both Fields', 'Custom Value');
-- optional_field = 'Custom Value'

INSERT INTO examples DEFAULT VALUES;
-- Error: required_field NOT NULL!

---

9.4 INSERT with SELECT

-- Create source table
CREATE TABLE products_archive (LIKE products INCLUDING ALL);

-- Insert from SELECT
INSERT INTO products_archive
SELECT * FROM products WHERE created_at < '2024-01-01';

-- Insert with transformation
INSERT INTO products (name, price, stock, category)
SELECT
    name || ' (Refurbished)',
    price * 0.8,
    stock,
    category
FROM products
WHERE stock > 10;

-- Insert from multiple sources
INSERT INTO products (name, price, stock)
SELECT name, price, stock FROM new_products
UNION ALL
SELECT name, price, stock FROM import_products;

-- Insert with computed values
INSERT INTO orders (user_id, total_amount, status)
SELECT
    id,
    (SELECT AVG(total_amount) FROM orders) * RANDOM() + 100,
    'pending'
FROM users
WHERE is_active = TRUE;

-- Insert with conditional logic
INSERT INTO product_stats (product_name, status, stock_level)
SELECT
    name,
    CASE
        WHEN stock = 0 THEN 'out_of_stock'
        WHEN stock < 10 THEN 'low_stock'
        ELSE 'in_stock'
    END,
    stock
FROM products;

---

9.5 INSERT with RETURNING

The RETURNING clause returns the inserted rows.

-- Return all columns of inserted row
INSERT INTO products (name, price, stock)
VALUES ('New Product', 99.99, 50)
RETURNING *;

-- Return specific columns
INSERT INTO products (name, price, stock)
VALUES ('Another Product', 49.99, 25)
RETURNING id, name;

-- Return with computed values
INSERT INTO users (username, email)
VALUES ('newuser', 'new@example.com')
RETURNING id, username, created_at;

-- Use in application code (common patterns)
-- PostgreSQL returns:
-- INSERT 0 1 with data

-- Insert and get ID for foreign key relationship
INSERT INTO orders (user_id, total_amount)
VALUES (1, 150.00)
RETURNING id AS order_id;

-- Then use that ID:
-- INSERT INTO order_items (order_id, product_id, quantity)
-- VALUES (5, 10, 2);

-- Multiple rows with RETURNING
INSERT INTO products (name, price)
VALUES
    ('Product A', 10.00),
    ('Product B', 20.00),
    ('Product C', 30.00)
RETURNING id, name;

-- Bulk insert with returning
INSERT INTO audit_log (action, record_id)
SELECT 'created', id FROM users
RETURNING action, record_id;

---

9.6 UPSERT (INSERT ON CONFLICT)

Handle duplicate key violations elegantly.

-- Basic UPSERT
INSERT INTO products (id, name, price, stock)
VALUES (1, 'Laptop', 1099.99, 60)
ON CONFLICT (id)
DO UPDATE SET
    price = EXCLUDED.price,
    stock = EXCLUDED.stock;

-- UPSERT with DO NOTHING
INSERT INTO products (id, name, price)
VALUES (1, 'Laptop', 1099.99)
ON CONFLICT (id) DO NOTHING;

-- UPSERT with specific constraint
INSERT INTO products (name, price)
VALUES ('Laptop', 1099.99)
ON CONFLICT (name)
DO UPDATE SET price = EXCLUDED.price;

-- Using unique constraint name
INSERT INTO products (name, price)
VALUES ('Mouse', 29.99)
ON CONFLICT (uk_products_name)
DO UPDATE SET price = EXCLUDED.price;

-- Complex UPSERT with multiple conditions
INSERT INTO users (username, email, is_active)
VALUES ('john_doe', 'john@example.com', TRUE)
ON CONFLICT (username)
DO UPDATE SET
    email = EXCLUDED.email,
    is_active = TRUE,
    updated_at = CURRENT_TIMESTAMP
WHERE users.is_active = FALSE;

-- UPSERT with RETURNING
INSERT INTO products (id, name, price)
VALUES (1, 'Updated Product', 99.99)
ON CONFLICT (id)
DO UPDATE SET price = EXCLUDED.price
RETURNING id, name, price,
    (xmax = 0) AS inserted;  -- TRUE if inserted, FALSE if updated

---

9.7 INSERT with ON CONFLICT DO UPDATE

Advanced conflict resolution patterns.

-- Increment counter pattern
INSERT INTO counters (key, count)
VALUES ('page_views', 1)
ON CONFLICT (key)
DO UPDATE SET count = counters.count + 1;

-- Aggregate into array
INSERT INTO user_actions (user_id, actions)
VALUES (1, ARRAY['login'])
ON CONFLICT (user_id)
DO UPDATE SET actions = user_actions.actions || EXCLUDED.actions;

-- Conditional upsert
INSERT INTO orders (user_id, total_amount, status)
VALUES (1, 150.00, 'pending')
ON CONFLICT (id)
DO UPDATE SET
    total_amount = orders.total_amount + EXCLUDED.total_amount,
    status = CASE
        WHEN orders.status = 'completed' THEN 'completed'
        ELSE 'pending'
    END;

-- Using subquery in DO UPDATE
INSERT INTO order_totals (order_id, total)
VALUES (1, 100.00)
ON CONFLICT (order_id)
DO UPDATE SET total = (
    SELECT SUM(total_amount)
    FROM orders
    WHERE id = EXCLUDED.order_id
);

-- Multiple conflict targets
INSERT INTO product_prices (product_id, price_type, price)
VALUES (1, 'retail', 99.99)
ON CONFLICT (product_id, price_type)
DO UPDATE SET price = EXCLUDED.price;

---

9.8 Bulk Insert Performance

-- Efficient multi-row INSERT
INSERT INTO products (name, price, stock) VALUES
    ('Product 1', 10.00, 100),
    ('Product 2', 20.00, 200),
    ('Product 3', 30.00, 300),
    ('Product 4', 40.00, 400),
    ('Product 5', 50.00, 500);

-- Copy data from file (most efficient for large datasets)
-- psql command:
-- \copy products (name, price, stock) FROM 'products.csv' WITH (FORMAT csv)

-- Or using SQL COPY
COPY products (name, price, stock)
FROM '/path/to/products.csv'
WITH (FORMAT csv, HEADER true);

-- Export to file
COPY products (name, price, stock)
TO '/path/to/export.csv'
WITH (FORMAT csv, HEADER true);

-- Batch inserts in application code
-- Instead of:
FOR product IN products LOOP
    INSERT INTO products VALUES (...);  -- N queries!
END LOOP;

-- Use batch:
INSERT INTO products (name, price) VALUES
    (p1.name, p1.price),
    (p2.name, p2.price),
    ...;  -- Single query!

---

9.9 Handling NULL Values

-- Insert with NULL
INSERT INTO products (name, description, price)
VALUES ('Product', NULL, 10.00);

-- Use COALESCE in returning
INSERT INTO products (name, description)
VALUES ('Product', 'Default description')
RETURNING id, COALESCE(description, 'No description') AS description;

-- Insert with ON CONFLICT handling NULLs
INSERT INTO products (name, description)
VALUES ('Product', NULL)
ON CONFLICT (name)
DO UPDATE SET description = COALESCE(EXCLUDED.description, products.description);

---

9.10 Best Practices

                    INSERT Best Practices
    ========================================================================

    1. Use Multi-Row INSERT
       ┌──────────────────────────────────────────────────────────────┐
       │  ❌ Single row per statement:                               │
       │  INSERT INTO products (name) VALUES ('a');                   │
       │  INSERT INTO products (name) VALUES ('b');                  │
       │                                                              │
       │  ✓ Batch multiple rows:                                      │
       │  INSERT INTO products (name) VALUES ('a'), ('b'), ('c');    │
       └──────────────────────────────────────────────────────────────┘

    2. Use RETURNING
       ┌──────────────────────────────────────────────────────────────┐
       │  • Get inserted IDs without additional query               │
       │  • Useful for foreign key relationships                     │
       │  • Reduces round trips                                      │
       └──────────────────────────────────────────────────────────────┘

    3. Use UPSERT for Synchronization
       ┌──────────────────────────────────────────────────────────────┐
       │  • Handle race conditions gracefully                       │
       │  • Simplify application logic                                │
       │  • Reduce error handling complexity                         │
       └──────────────────────────────────────────────────────────────┘

    4. Use COPY for Large Data
       ┌──────────────────────────────────────────────────────────────┐
       │  • Much faster than INSERT for bulk loading                 │
       │  • Use for initial data loads, migrations                   │
       │  • Consider parallel loading for very large datasets        │
       └──────────────────────────────────────────────────────────────┘

    5. Validate Before Insert
       ┌──────────────────────────────────────────────────────────────┐
       │  • Use CHECK constraints                                    │
       │  • Use triggers for complex validation                      │
       │  • Consider NOT NULL constraints                            │
       └──────────────────────────────────────────────────────────────┘
    ========================================================================

---

Next Chapter

Chapter 10: UPDATE - Modifying Data

---

Last Updated: February 2026