INSERT - Adding New Data
Introduction
So far, you've learned to query existing data. Now it's time to create your own! The INSERT statement adds new rows to tables. Whether you're adding customers, products, or blog posts, INSERT is how you populate your database.
The INSERT Statement
Basic Syntax
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);
Your First INSERT
INSERT INTO users (name, email, age)
VALUES ('John Doe', 'john@example.com', 42);
Result:
INSERT 0 1
This means: 1 row was inserted successfully.
Verify the Insert
SELECT * FROM users WHERE name = 'John Doe';
Output:
id | name | email | age
----+----------+--------------------+-----
7 | John Doe | john@example.com | 42
Notice the id was automatically assigned (thanks to SERIAL).
Inserting Multiple Rows
Single INSERT with Multiple VALUES
INSERT INTO users (name, email, age)
VALUES
('Kate Wilson', 'kate@example.com', 29),
('Luke Brown', 'luke@example.com', 35),
('Mia Garcia', 'mia@example.com', 27);
Result:
INSERT 0 3
Three rows inserted at once—much faster than three separate INSERT statements!
Verify
SELECT * FROM users WHERE name IN ('Kate Wilson', 'Luke Brown', 'Mia Garcia');
Column Order
Order Matters
The values must match the column order you specify:
INSERT INTO users (name, email, age)
VALUES ('Nina Patel', 'nina@example.com', 31);
-- ↓ ↓ ↓
-- name email age
You Can Change the Order
INSERT INTO users (age, name, email)
VALUES (38, 'Oscar Lee', 'oscar@example.com');
-- ↓ ↓ ↓
-- age name email
Just make sure VALUES match your specified column order!
Omitting Columns
Auto-Generated Columns
You don't need to specify columns with default values:
-- id is auto-generated (SERIAL), so we skip it
INSERT INTO users (name, email, age)
VALUES ('Paula Kim', 'paula@example.com', 26);
Columns Allowing NULL
-- Email is optional (allows NULL)
INSERT INTO users (name, age)
VALUES ('Quinn Roberts', 33);
-- Verify
SELECT * FROM users WHERE name = 'Quinn Roberts';
Output:
id | name | email | age
----+----------------+-------+-----
11 | Quinn Roberts | | 33
Email is NULL (empty).
DEFAULT Values
If a column has a default value:
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10,2) DEFAULT 0.00,
in_stock BOOLEAN DEFAULT true
);
INSERT INTO products (name)
VALUES ('Widget');
SELECT * FROM products;
Output:
id | name | price | in_stock
----+--------+-------+----------
1 | Widget | 0.00 | t
price and in_stock use their default values.
Inserting All Columns
Shorthand Syntax
If inserting values for all columns in order:
-- Full syntax
INSERT INTO users (id, name, email, age)
VALUES (DEFAULT, 'Rachel Stone', 'rachel@example.com', 29);
-- Shorthand (all columns, in table order)
INSERT INTO users
VALUES (DEFAULT, 'Rachel Stone', 'rachel@example.com', 29);
Warning: This is error-prone. If the table structure changes, your INSERT breaks. Prefer explicitly listing columns.
RETURNING Clause
Get Inserted Data Back
INSERT INTO users (name, email, age)
VALUES ('Sam Taylor', 'sam@example.com', 40)
RETURNING *;
Output:
id | name | email | age
----+------------+-------------------+-----
12 | Sam Taylor | sam@example.com | 40
Returns the inserted row immediately!
Return Specific Columns
INSERT INTO users (name, email, age)
VALUES ('Tina Martinez', 'tina@example.com', 24)
RETURNING id, name;
Output:
id | name
----+---------------
13 | Tina Martinez
Use cases:
- Get the auto-generated
idfor use in application code - Confirm inserted values
- Audit logging
Inserting from SELECT
Copy Data from Another Table
-- Create a backup table
CREATE TABLE users_backup AS SELECT * FROM users WHERE 1=0;
-- (Creates table structure with no rows)
-- Copy all users over 30
INSERT INTO users_backup (name, email, age)
SELECT name, email, age
FROM users
WHERE age > 30;
-- Verify
SELECT COUNT(*) FROM users_backup;
This inserts the result of a SELECT query into another table.
Handling Constraints
PRIMARY KEY Violations
-- Create table with manual ID
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(100)
);
INSERT INTO customers (id, name) VALUES (1, 'Customer A');
INSERT INTO customers (id, name) VALUES (1, 'Customer B'); -- ❌ Error!
Error:
ERROR: duplicate key value violates unique constraint "customers_pkey"
DETAIL: Key (id)=(1) already exists.
Solution: Use different IDs or use SERIAL for auto-generation.
NOT NULL Violations
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100)
);
INSERT INTO employees (email) VALUES ('emp@example.com'); -- ❌ Error!
Error:
ERROR: null value in column "name" violates not-null constraint
Solution: Provide a value for required columns.
UNIQUE Violations
CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
email VARCHAR(100) UNIQUE
);
INSERT INTO accounts (email) VALUES ('test@example.com');
INSERT INTO accounts (email) VALUES ('test@example.com'); -- ❌ Error!
Error:
ERROR: duplicate key value violates unique constraint "accounts_email_key"
Solution: Use unique values or handle conflicts (see ON CONFLICT below).
ON CONFLICT (Upsert)
Insert or Update (PostgreSQL)
CREATE TABLE inventory (
product_id INTEGER PRIMARY KEY,
quantity INTEGER
);
-- First insert
INSERT INTO inventory (product_id, quantity)
VALUES (101, 50);
-- Try inserting again (would fail)
-- Instead, update if exists:
INSERT INTO inventory (product_id, quantity)
VALUES (101, 30)
ON CONFLICT (product_id)
DO UPDATE SET quantity = EXCLUDED.quantity;
SELECT * FROM inventory;
Output:
product_id | quantity
------------+----------
101 | 30
Quantity was updated to 30!
Do Nothing on Conflict
INSERT INTO inventory (product_id, quantity)
VALUES (101, 99)
ON CONFLICT (product_id)
DO NOTHING;
If product_id 101 exists, the INSERT is silently ignored.
Common Patterns
Pattern 1: Insert with Current Timestamp
CREATE TABLE logs (
id SERIAL PRIMARY KEY,
message TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO logs (message) VALUES ('User logged in');
SELECT * FROM logs;
created_at is automatically set to the current time.
Pattern 2: Insert from Application Variables
In real applications, you'd use parameterized queries:
# Python example (using psycopg2)
cursor.execute(
"INSERT INTO users (name, email, age) VALUES (%s, %s, %s)",
(user_name, user_email, user_age)
)
Never concatenate user input directly into SQL (SQL injection risk)!
Pattern 3: Bulk Insert for Performance
INSERT INTO large_table (col1, col2, col3)
VALUES
(1, 'a', 100),
(2, 'b', 200),
(3, 'c', 300),
-- ... thousands more ...
(10000, 'z', 1000000);
Much faster than 10,000 separate INSERT statements.
Practice Exercises
Exercise 1
Insert a new user named "Victor Chen" with email "victor@example.com" and age 45.
Solution
INSERT INTO users (name, email, age)
VALUES ('Victor Chen', 'victor@example.com', 45);
Exercise 2
Insert three new users at once.
Solution
INSERT INTO users (name, email, age)
VALUES
('Wendy Adams', 'wendy@example.com', 32),
('Xander Brooks', 'xander@example.com', 28),
('Yara Collins', 'yara@example.com', 36);
Exercise 3
Insert a user without an email address.
Solution
INSERT INTO users (name, age)
VALUES ('Zoe Davis', 29);
Exercise 4
Insert a user and return their auto-generated ID.
Solution
INSERT INTO users (name, email, age)
VALUES ('Aaron Foster', 'aaron@example.com', 41)
RETURNING id;
Key Takeaways
- ✅ INSERT INTO adds new rows to tables
- ✅ VALUES specifies the data to insert
- ✅ Multiple rows can be inserted in one statement
- ✅ Column order must match VALUES order
- ✅ Omit columns with defaults or allowing NULL
- ✅ RETURNING gives back inserted data
- ✅ ON CONFLICT handles duplicate key conflicts (upsert)
Next Steps
You can now add data to your database! But what if you need to change existing data? In the next lesson, we'll learn UPDATE—how to modify records that already exist.
Let's keep going!