iwantcoding.com
🔥 Daily 👥 Rooms 🏆 Top Log in Sign up

INSERT

By · Reviewed & last updated · Part of PostgreSQL Tutorial

INSERT adds rows. The RETURNING clause is the killer feature — Postgres gives you the inserted rows back (including generated IDs and defaults) in one round trip.

Single, multi, on-conflict, returning

EXAMPLE
-- Single row
INSERT INTO users (email, name)
VALUES ('ada@example.com', 'Ada')
RETURNING id, created_at;

-- Multiple rows in one statement
INSERT INTO users (email, name) VALUES
    ('ada@example.com', 'Ada'),
    ('bo@example.com',  'Bo'),
    ('cy@example.com',  'Cy')
RETURNING id, email;

-- INSERT … SELECT — copy from another table
INSERT INTO posts_archive (id, title, body, archived_at)
SELECT id, title, body, now()
FROM   posts
WHERE  created_at < now() - INTERVAL '1 year';

-- ON CONFLICT — upsert
INSERT INTO users (email, name)
VALUES ('ada@example.com', 'Ada Lovelace')
ON CONFLICT (email) DO UPDATE
    SET name = EXCLUDED.name,
        updated_at = now()
RETURNING id, name;

-- ON CONFLICT DO NOTHING — idempotent insert
INSERT INTO unique_tags (tag) VALUES ('sql')
ON CONFLICT (tag) DO NOTHING
RETURNING id;

Why it matters

RETURNING + parameterised queries = no second SELECT to fetch the new id. Most ORMs use it under the hood when targeting Postgres.

Tip: Tweak the snippet with Try it Yourself », then sit the quiz at the bottom of the page.

Example

Example
INSERT INTO users (email, name)
VALUES ('ada@example.com', 'Ada')
RETURNING id, created_at;
Try it Yourself »

Discussion

Loading…