INSERT¶
Basic INSERT¶
Single row¶
Multi-row¶
INSERT INTO users (email, name) VALUES
('bob@example.com', 'Bob'),
('carol@example.com', 'Carol'),
('dave@example.com', 'Dave');
Using DEFAULT values¶
Omit columns that have defaults — they are filled in automatically:
-- active defaults to true, created defaults to NOW()
INSERT INTO users (email, name) VALUES ('eve@example.com', 'Eve');
-- Explicit NOW()
INSERT INTO events (name, created) VALUES ('login', NOW());
Bind parameters¶
Always use ? placeholders for user-supplied values:
Prepared statements¶
stmt, err := db.Prepare(`INSERT INTO users (email, name) VALUES (?, ?)`)
defer stmt.Close()
for _, u := range users {
_, err = stmt.Exec(u.Email, u.Name)
}
INSERT INTO … SELECT¶
Populate a table from a query result instead of a literal VALUES list.
INSERT INTO table_name (col1, col2, ...)
SELECT expr1, expr2, ...
FROM source_table
[WHERE condition];
The column list is required. The number of SELECT output columns must match the number of target columns.
Copy all rows¶
Copy a filtered subset¶
INSERT INTO archived_users (email, name)
SELECT email, name FROM users
WHERE created < '2024-01-01';
Copy a transformed subset¶
INSERT INTO audit_log (user_id, action, ts)
SELECT id, 'signup', created FROM users
WHERE created > '2025-01-01';
Bulk move between tables¶
INSERT INTO orders_archive (order_id, user_id, total_paid)
SELECT order_id, user_id, total_paid FROM orders
WHERE created < '2024-01-01';
DELETE FROM orders WHERE created < '2024-01-01';
With ON CONFLICT¶
With RETURNING¶
rows, err := db.Query(`
INSERT INTO archived_users (email, name)
SELECT email, name FROM users WHERE id = ?
RETURNING id, email
`, userID)
defer rows.Close()
for rows.Next() {
var id int64
var email string
rows.Scan(&id, &email)
}
Seeding from a subquery¶
INSERT INTO product_stats (product_id, total_orders)
SELECT product_id, COUNT(*) FROM orders
GROUP BY product_id;
ON CONFLICT¶
DO NOTHING¶
Silently skip the row if a unique or primary-key constraint is violated:
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice Duplicate')
ON CONFLICT DO NOTHING;
DO UPDATE (upsert)¶
Update the existing row when there is a conflict:
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice Updated')
ON CONFLICT DO UPDATE SET name = 'Alice Updated';
Use the EXCLUDED pseudo-table to reference the values that were proposed for insertion:
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice V2')
ON CONFLICT DO UPDATE SET name = EXCLUDED.name;
Multi-row upsert with EXCLUDED:
INSERT INTO users (email, name) VALUES
('alice@example.com', 'Alice V3'),
('bob@example.com', 'Bob V3')
ON CONFLICT DO UPDATE SET name = EXCLUDED.name;
Update multiple columns:
INSERT INTO products (sku, name, price, stock)
VALUES ('ABC-1', 'Widget', 999, 100)
ON CONFLICT DO UPDATE
SET name = EXCLUDED.name,
price = EXCLUDED.price,
stock = stock + EXCLUDED.stock;
RETURNING¶
RETURNING makes INSERT behave like a query — it returns rows from the newly inserted data.
Return the generated primary key¶
var newID int64
err = db.QueryRow(
`INSERT INTO users (email, name) VALUES (?, ?) RETURNING id`,
"alice@example.com", "Alice",
).Scan(&newID)
Return multiple columns¶
INSERT INTO users (email, name)
VALUES ('bob@example.com', 'Bob')
RETURNING id, name, email, created;
Multi-row RETURNING¶
rows, err := db.Query(`
INSERT INTO users (email, name) VALUES
('carol@example.com', 'Carol'),
('dave@example.com', 'Dave')
RETURNING id, name
`)
defer rows.Close()
for rows.Next() {
var id int64
var name string
rows.Scan(&id, &name)
}
ON CONFLICT … RETURNING¶
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT DO UPDATE SET name = EXCLUDED.name
RETURNING id, name;
Inserting JSON¶
INSERT INTO events (name, payload)
VALUES ('login', '{"user":"alice","uid":42}');
INSERT INTO events (name, payload)
VALUES ('tags', '["go","sql","json"]');
Inserting VECTOR data¶
// String literal
_, err = db.Exec(
`INSERT INTO documents (body, embedding) VALUES (?, ?)`,
"hello world", "[0.1, 0.2, 0.3]",
)
// []float32 bind parameter
vec := []float32{0.1, 0.2, 0.3}
_, err = db.Exec(
`INSERT INTO documents (body, embedding) VALUES (?, ?)`,
"hello world", vec,
)
Inserting UUID¶
Or use CAST: