INSERT and RETURNING

PostgreSQL's INSERT statement supports inserting a single row, multiple rows in one statement, and inserting the result of a SELECT query. The RETURNING clause is a powerful PostgreSQL extension to standard SQL that returns column values from the inserted rows — extremely useful for retrieving the auto-generated primary key or computed default values without a second round trip. ON CONFLICT DO NOTHING or ON CONFLICT DO UPDATE (upsert) handles situations where a row with the same unique key already exists, allowing you to either skip the insert or update the existing row atomically. The DO UPDATE form uses the EXCLUDED pseudo-table to reference the values that were proposed for insertion.

Example

-- Basic INSERT
INSERT INTO users (name, email, created_at)
VALUES ('Alice', 'alice@example.com', NOW());

-- INSERT with RETURNING — get the generated id back
INSERT INTO users (name, email)
VALUES ('Bob', 'bob@example.com')
RETURNING id, created_at;

-- Multi-row INSERT
INSERT INTO products (name, price, stock) VALUES
  ('Keyboard',  79.99, 100),
  ('Mouse',     39.99, 150),
  ('Headset',  129.99,  60)
RETURNING id, name;

-- INSERT ... SELECT — copy rows from another table
INSERT INTO archived_orders (id, user_id, total, archived_at)
SELECT id, user_id, total, NOW()
FROM orders
WHERE created_at < NOW() - INTERVAL '2 years';

-- UPSERT — ON CONFLICT DO UPDATE
INSERT INTO user_settings (user_id, theme, language)
VALUES (42, 'dark', 'en')
ON CONFLICT (user_id) DO UPDATE
  SET theme    = EXCLUDED.theme,
      language = EXCLUDED.language,
      updated_at = NOW();

-- ON CONFLICT DO NOTHING — ignore duplicate key errors
INSERT INTO tags (name) VALUES ('postgresql'), ('databases'), ('postgresql')
ON CONFLICT (name) DO NOTHING;

HTML

Full Editor

Related Resources

PostgreSQL Reference

Complete tag & property list

PostgreSQL How-To Guides

Step-by-step practical guides

PostgreSQL Exercises

Practice what you've learned