INSERT: Adding Rows

~10 min · queries, write

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Filling out a form

INSERT adds rows. PostgreSQL validates every value against the schema (types, constraints, foreign keys) before the row lands — bad inserts are rejected at the door, not silently corrupting your data later.

Always list the columns

INSERT INTO users VALUES (...) without column names depends on column order in the table definition. Add a column tomorrow and every such INSERT breaks. Always list the columns you're inserting; future-you will thank you.

Multi-row inserts and UPSERTs

One INSERT with many rows is dramatically faster than many INSERTs in a loop — fewer round trips, less per-row overhead. ON CONFLICT handles "insert this row, but if it conflicts on a unique key, update instead" atomically.

Code

Single, multi-row, and from SELECT·sql

-- Single row (always list columns)
INSERT INTO users (name, email, role)
VALUES ('Alice Johnson', 'alice@example.com', 'admin');

-- Multi-row (one statement, many rows)
INSERT INTO users (name, email, role) VALUES
    ('Bob',   'bob@example.com',   'user'),
    ('Carol', 'carol@example.com', 'user'),
    ('Dave',  'dave@example.com',  'moderator');

-- INSERT ... SELECT (copy from another table)
INSERT INTO orders_archive (id, customer_id, total, placed_at)
SELECT id, customer_id, total, placed_at
FROM   orders
WHERE  placed_at < CURRENT_DATE - INTERVAL '1 year';

Upsert with ON CONFLICT·sql

-- Insert; on email conflict, update name + role.
INSERT INTO users (email, name, role)
VALUES ('alice@example.com', 'Alice J.', 'admin')
ON CONFLICT (email) DO UPDATE
SET name = EXCLUDED.name,
    role = EXCLUDED.role;

-- Insert; on conflict, do nothing (silently skip duplicates).
INSERT INTO tags (name) VALUES ('postgres'), ('sql'), ('postgres')
ON CONFLICT (name) DO NOTHING;

External links

PostgreSQL INSERT

Exercise

Write an INSERT that adds 5 rows in one statement. Then write the same inserts again with ON CONFLICT DO NOTHING and verify that running them twice doesn't produce duplicates or errors.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousORDER BY, LIMIT, and Pagination Next →UPDATE and DELETE Without Tears

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.