INSERT — Creating Rows¶

The INSERT statement is the C in CRUD at the database level.

Basic Insert¶

1INSERT INTO tasks (title, status, due_date)
2VALUES ('Write tests', 'TODO', '2026-07-01');

You list the columns, then the matching values. Columns you omit get their DEFAULT (or NULL).

Inserting Multiple Rows¶

1INSERT INTO tasks (title, status) VALUES
2  ('Design schema', 'DONE'),
3  ('Build API',     'IN_PROGRESS'),
4  ('Write docs',    'TODO');

One statement, three rows — far faster than three separate inserts.

Returning the New Row¶

By default INSERT returns only a row count. PostgreSQL can hand back the created row — essential for getting the generated id:

1INSERT INTO tasks (title, status)
2VALUES ('Ship it', 'TODO')
3RETURNING id, created_at;

Upsert: Insert or Update¶

Sometimes you want "insert if new, update if it already exists":

1INSERT INTO tags (name) VALUES ('urgent')
2ON CONFLICT (name) DO NOTHING;
3
4INSERT INTO settings (user_id, theme) VALUES (1, 'dark')
5ON CONFLICT (user_id) DO UPDATE SET theme = EXCLUDED.theme;

EXCLUDED refers to the row you tried to insert. Upserts make Create operations idempotent.

Common Pitfalls¶

• Column/value mismatch — the count and order must line up.
• Violating NOT NULL — omitting a required column with no default errors out.
• Violating UNIQUE — inserting a duplicate email fails unless you handle the conflict.
• Trusting client IDs — let the database generate keys; don't accept an id from the client on create.