INSERT — Adding Rows and Using RETURNING

The INSERT statement adds new rows to a table. In a FastAPI CRUD application, every POST endpoint eventually executes an INSERT. What makes PostgreSQL’s INSERT particularly powerful is the RETURNING clause — you can get back the generated primary key, computed defaults like created_at, or any other column immediately after insertion, without a separate SELECT query. The ON CONFLICT clause enables upsert operations — insert if not exists, update if exists — which is a common requirement in real applications.

Basic INSERT #

-- Insert a single row
INSERT INTO users (email, name, role)
VALUES ('alice@example.com', 'Alice Smith', 'admin');

-- Insert multiple rows at once (more efficient than separate INSERTs)
INSERT INTO tags (name, slug)
VALUES
    ('python',     'python'),
    ('fastapi',    'fastapi'),
    ('postgresql', 'postgresql'),
    ('react',      'react');

-- Insert with all defaults (no columns specified — uses all defaults)
INSERT INTO users (email, name) VALUES ('bob@example.com', 'Bob Jones');
-- role defaults to 'user', is_active to TRUE, created_at to NOW()

RETURNING — Get Inserted Data Back #

-- Return the generated id after insert
INSERT INTO users (email, name)
VALUES ('charlie@example.com', 'Charlie Kim')
RETURNING id;
-- Returns: id = 3

-- Return multiple columns including defaults
INSERT INTO posts (author_id, title, slug, body)
VALUES (1, 'Hello World', 'hello-world', 'My first post.')
RETURNING id, public_id, created_at, status;
-- Returns: id=1, public_id="a8098c1a...", created_at="2025-08-06...", status="draft"

-- Return the entire inserted row
INSERT INTO tags (name, slug) VALUES ('javascript', 'javascript')
RETURNING *;

-- In Python with psycopg2:
-- cursor.execute("INSERT INTO users (email, name) VALUES (%s, %s) RETURNING id",
--               ("alice@example.com", "Alice"))
-- user_id = cursor.fetchone()[0]

INSERT … ON CONFLICT — Upsert #

-- ON CONFLICT DO NOTHING — ignore if constraint violated (no error raised)
INSERT INTO tags (name, slug)
VALUES ('python', 'python')
ON CONFLICT (name) DO NOTHING;
-- If a tag named 'python' already exists: no error, no insert, no update

-- ON CONFLICT DO UPDATE — update on duplicate (upsert)
INSERT INTO users (email, name, role)
VALUES ('alice@example.com', 'Alice Updated', 'editor')
ON CONFLICT (email) DO UPDATE
    SET name = EXCLUDED.name,     -- EXCLUDED refers to the row we tried to insert
        role = EXCLUDED.role,
        updated_at = NOW();
-- If email already exists: updates name and role; otherwise inserts normally

-- Upsert with conditional update
INSERT INTO post_views (post_id, ip_address, viewed_at)
VALUES (1, '192.168.1.1', NOW())
ON CONFLICT (post_id, ip_address) DO UPDATE
    SET view_count = post_views.view_count + 1,  -- reference the EXISTING row
        last_viewed = NOW();

-- ON CONFLICT on a specific constraint name
INSERT INTO post_tags (post_id, tag_id)
VALUES (1, 5)
ON CONFLICT ON CONSTRAINT post_tags_pkey DO NOTHING;

INSERT … SELECT — Copy Data #

-- Insert rows from a SELECT query
INSERT INTO post_archive (id, title, body, author_id, archived_at)
SELECT id, title, body, author_id, NOW()
FROM posts
WHERE status = 'archived';

-- Useful for: archiving, migrations, creating summary tables
-- The SELECT can include JOINs, WHERE, and any other clauses

Common Mistakes #

Mistake 1 — Not using RETURNING to get the generated ID #

❌ Wrong — separate SELECT after INSERT (race condition, extra round-trip):

INSERT INTO users (email, name) VALUES ('x@example.com', 'X');
SELECT id FROM users WHERE email = 'x@example.com';   -- separate query!

✅ Correct — RETURNING gives you the ID in one query:

INSERT INTO users (email, name) VALUES ('x@example.com', 'X')
RETURNING id;   -- ✓ one round-trip

Mistake 2 — Not handling IntegrityError for unique violations #

❌ Wrong — FastAPI returns 500 on duplicate email:

INSERT INTO users (email, name) VALUES ('alice@example.com', 'Duplicate');
-- psycopg2.errors.UniqueViolation → unhandled → 500 Internal Server Error

✅ Correct — catch and return 409 in FastAPI:

try:
    db.execute(insert_query)
except IntegrityError:
    raise HTTPException(409, "Email already registered")   # ✓

Mistake 3 — Forgetting column names in multi-row INSERT #

❌ Wrong — column names omitted, positional mapping is fragile:

INSERT INTO users VALUES (DEFAULT, 'x@x.com', 'X', 'hash', 'user', TRUE, NOW(), NOW());
-- Breaks if column order changes in the schema!

✅ Correct — always name the columns:

INSERT INTO users (email, name, password_hash)
VALUES ('x@x.com', 'X', 'hash');   -- ✓ explicit, robust to schema changes

Quick Reference #