Inserting Data
1. Inserting Single Row
INSERT INTO customers (email, full_name)
VALUES ('alice@example.com', 'Alice Walker');
| Clause | Required |
|---|
| Column list | Recommended (forward-compatible) |
| VALUES tuple | Same arity as column list |
2. Inserting Multiple Rows
INSERT INTO customers (email, full_name) VALUES
('bob@example.com', 'Bob Lee'),
('cara@example.com', 'Cara Chen'),
('dan@example.com', 'Dan Patel');
Note: Multi-row INSERT is much faster than many single-row inserts (one network round-trip, one WAL record per batch).
3. Inserting Specific Columns
INSERT INTO orders (customer_id, total_cents)
VALUES (42, 1999);
-- omitted columns use DEFAULT or NULL
4. Inserting from SELECT Statement
INSERT INTO orders_archive (id, customer_id, total_cents, placed_at)
SELECT id, customer_id, total_cents, placed_at
FROM orders
WHERE placed_at < now() - interval '1 year';
| Tip | Why |
|---|
| Same column count | SELECT arity must match INSERT column list |
| No VALUES needed | Either VALUES or SELECT, not both |
5. Using DEFAULT Keyword
INSERT INTO orders (customer_id, status, total_cents)
VALUES (42, DEFAULT, 1999);
6. Using DEFAULT VALUES Clause
INSERT INTO audit_events DEFAULT VALUES;
| Use | Detail |
|---|
| All defaults | Every column must have DEFAULT or accept NULL |
7. Using RETURNING Clause
INSERT INTO customers (email, full_name)
VALUES ('eve@example.com', 'Eve Ng')
RETURNING id, created_at;
| Returns | Notes |
|---|
| * | All columns of inserted row(s) |
| Expressions | Computed from new row |
8. Using OVERRIDING SYSTEM VALUE
INSERT INTO orders (id, customer_id, total_cents)
OVERRIDING SYSTEM VALUE
VALUES (1001, 42, 1999);
Note: Required when column was declared GENERATED ALWAYS AS IDENTITY and you must supply an explicit value.
9. Using OVERRIDING USER VALUE
INSERT INTO orders (id, customer_id, total_cents)
OVERRIDING USER VALUE
VALUES (9999, 42, 1999); -- user value 9999 IGNORED; sequence used
| Mode | Effect on GENERATED BY DEFAULT |
|---|
| SYSTEM VALUE override | Force use of sequence even when user provided value |
| USER VALUE override | Ignore user value, use sequence |
10. Inserting Array Values
INSERT INTO posts (title, tags) VALUES
('Welcome', ARRAY['intro','blog']),
('Tips', '{"sql","perf"}');
11. Inserting JSON Values
INSERT INTO events (payload) VALUES
('{"type":"signup","user_id":42}'::jsonb),
(jsonb_build_object('type','login','at',now()));
Warning: Plain text inserts validate JSON syntax but skip schema checks; consider jsonschema-style triggers if structure must be enforced.