PostgreSQL INSERT INTO

Overview

The INSERT statement adds new rows to a PostgreSQL table. This chapter covers various ways to insert data, including single rows, multiple rows, and inserting from other tables.

INSERT Syntax

INSERT INTO table_name (columns) VALUES (values);
INSERT INTO table_name DEFAULT VALUES;
INSERT INTO table_name SELECT ...;

Basic INSERT Operations

Single Row

-- Specify columns
INSERT INTO users (name, email, age) 
VALUES ('John Doe', 'john@example.com', 30);

-- All columns (order matters)
INSERT INTO users VALUES (1, 'John Doe', 'john@example.com', 30, NOW());

-- With DEFAULT
INSERT INTO users (name, email) VALUES ('Jane', 'jane@example.com');

Multiple Rows

-- Single INSERT statement
INSERT INTO products (name, price, category) VALUES 
    ('Product A', 19.99, 'Electronics'),
    ('Product B', 29.99, 'Electronics'),
    ('Product C', 39.99, 'Clothing'),
    ('Product D', 49.99, 'Books'),
    ('Product E', 59.99, 'Sports');

-- Can insert hundreds of rows efficiently

INSERT with SELECT

-- Copy from another table
INSERT INTO archive_users 
SELECT * FROM users WHERE deleted_at IS NOT NULL;

-- Specific columns
INSERT INTO user_summary (user_id, name, email)
SELECT id, name, email FROM users WHERE status = 'active';

-- With transformation
INSERT INTO daily_stats (stat_date, user_count)
SELECT CURRENT_DATE, COUNT(*) FROM users;

Conflict Handling

Basic ON CONFLICT

-- Do nothing on conflict
INSERT INTO users (id, name, email) VALUES (1, 'John', 'john@example.com')
ON CONFLICT (id) DO NOTHING;

-- Update on conflict
INSERT INTO users (id, name, email) VALUES (1, 'John Updated', 'john.new@example.com')
ON CONFLICT (id) DO UPDATE SET 
    name = EXCLUDED.name,
    email = EXCLUDED.email,
    updated_at = NOW();

ON CONFLICT with WHERE

-- Conditional update
INSERT INTO products (id, name, price) VALUES (1, 'Product', 29.99)
ON CONFLICT (id) DO UPDATE SET 
    price = EXCLUDED.price
    WHERE products.stock > 0;

INSERT with RETURNING

-- Return inserted values
INSERT INTO users (name, email) VALUES ('New User', 'new@example.com')
RETURNING id, name, created_at;

-- Return all columns
INSERT INTO orders (user_id, total) VALUES (1, 99.99)
RETURNING *;

-- Return specific expression
INSERT INTO products (name, price) VALUES ('Test', 9.99)
RETURNING id, price * 1.1 AS price_with_tax;

Summary

INSERT in PostgreSQL includes:

• Basic INSERT, Multiple rows
• INSERT with SELECT
• ON CONFLICT for upsert operations
• RETURNING clause for results