Inserting Records with INSERT
Learn SQL Data Manipulation Language (DML) by inserting single and multiple records, using defaults, handling constraints, and retrieving inserted values with PostgreSQL RETURNING.
In this lesson, you will learn
- ✓Explain the purpose of INSERT as a Data Manipulation Language statement
- ✓Insert a single record by specifying columns and values
- ✓Insert multiple records with one INSERT statement
- ✓Understand how omitted columns, DEFAULT, and NULL behave
- ✓Use PostgreSQL RETURNING to retrieve inserted values and generated IDs
- ✓Recognize common constraint violations and correct them
- ✓Insert rows using INSERT INTO ... SELECT
- ✓Use transactions to control related data changes
- ✓Follow safe practices when inserting data from application code
1. Introduction to INSERT
SQL statements are commonly grouped into categories according to what they do.
- DDL (Data Definition Language): defines database structures, such as CREATE TABLE.
- DML (Data Manipulation Language): changes table data, such as INSERT, UPDATE, and DELETE.
- DQL (Data Query Language): retrieves data, commonly using SELECT.
In this lesson, we focus on INSERT.
### What does INSERT do?
INSERT adds new rows to an existing table.
Imagine a students table:
| student_id | student_name | age |
|---:|---|---:|
| 1 | Asha | 20 |
| 2 | Rahul | 21 |
You want to add Neha. Instead of replacing the table, you insert another row.
### General syntax
```sql
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);
```
Read it like this:
1. INSERT INTO identifies the table.
2. The column list specifies where values belong.
3. VALUES provides the data for the new row.
The column list is strongly recommended because it makes your intent explicit and avoids relying on the table's physical column order.
2. Create a Practice Table
We will use a students table throughout this lesson.
### Create the table
```sql
CREATE TABLE students (
student_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
student_name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE,
age INTEGER CHECK (age >= 16),
city VARCHAR(80) DEFAULT 'Unknown',
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
```
### Understand the columns
- student_id: PostgreSQL generates the ID automatically.
- student_name: required text.
- email: optional, but duplicate non-NULL email values are not allowed.
- age: can be NULL, but a non-NULL value must be at least 16.
- city: defaults to 'Unknown' when omitted.
- created_at: defaults to the current transaction timestamp.
The identity column generates values, while PRIMARY KEY enforces uniqueness and disallows NULL.
### Inspect the table
In the PostgreSQL command-line client, psql:
```sql
d students
```
The backslash command is a psql command, not ordinary SQL. If you use a graphical database client, inspect the table through its schema or structure view instead.
3. Insert a Single Record
Let's insert one student.
```sql
INSERT INTO students (student_name, email, age, city)
VALUES ('Asha', 'asha@example.com', 20, 'Lucknow');
```
The statement adds one row.
Notice that student_id and created_at are omitted. PostgreSQL generates the identity value and applies the timestamp default.
### Check the inserted record
```sql
SELECT student_id, student_name, email, age, city, created_at
FROM students;
```
The generated student_id and created_at depend on the database state and execution time, so they should not be assumed to have particular values.
### Why specify the column names?
Consider:
```sql
INSERT INTO students
VALUES (1, 'Asha', 'asha@example.com', 20, 'Lucknow', CURRENT_TIMESTAMP);
```
This relies on the table's complete column order and explicitly supplies the ID and timestamp. It is less robust and may conflict with an identity definition.
Prefer:
```sql
INSERT INTO students (student_name, email, age, city)
VALUES ('Asha', 'asha@example.com', 20, 'Lucknow');
```
Explicit column lists make statements easier to understand and maintain.
Insert one student
The INSERT creates the row, and the SELECT retrieves it. The expected row assumes the email was not already present and the statements succeed.
4. Insert Multiple Records
You can insert several rows with a single INSERT statement.
### Example
```sql
INSERT INTO students (student_name, email, age, city)
VALUES
('Rahul', 'rahul@example.com', 21, 'Noida'),
('Neha', 'neha@example.com', 22, 'Delhi'),
('Kabir', 'kabir@example.com', 19, 'Lucknow');
```
Each parenthesized group represents one row.
### Verify the inserted rows
```sql
SELECT student_name, email, age, city
FROM students
WHERE email IN (
'rahul@example.com',
'neha@example.com',
'kabir@example.com'
)
ORDER BY student_name;
```
Expected output:
| student_name | email | age | city |
|---|---|---:|---|
| Kabir | kabir@example.com | 19 | Lucknow |
| Neha | neha@example.com | 22 | Delhi |
| Rahul | rahul@example.com | 21 | Noida |
### Why use multi-row INSERT?
- It reduces repetitive SQL statements.
- It can reduce database round trips compared with sending separate INSERT statements.
- It can be convenient for importing a small or moderate batch of records.
The actual performance benefit depends on the client, transaction handling, constraints, triggers, indexes, and workload.
For very large data imports, PostgreSQL's COPY command may be more appropriate. COPY is covered separately from ordinary INSERT syntax.
Insert several students
In PostgreSQL, the command tag reports that three rows were inserted. The example assumes the supplied emails are unique.
5. Using DEFAULT and Omitting Columns
A column does not always need an explicit value.
PostgreSQL can use a declared default when a column is omitted from the INSERT column list.
### Example: Use the city default
```sql
INSERT INTO students (student_name, email, age)
VALUES ('Meera', 'meera@example.com', 23);
```
The city column receives 'Unknown', and created_at receives its default.
### Explicitly request a default
```sql
INSERT INTO students (student_name, email, age, city)
VALUES ('Arjun', 'arjun@example.com', 24, DEFAULT);
```
Here, DEFAULT tells PostgreSQL to use the column's default expression.
### What if a column has no default?
If you omit a column that allows NULL and has no default, it normally receives NULL. If it is NOT NULL and has no applicable default, the insert fails.
### Omitted value vs DEFAULT vs NULL
| Input | Meaning |
|---|---|
| Omit the column | Use its default, or NULL if allowed and no default applies |
| DEFAULT | Request the declared default |
| NULL | Explicitly supply NULL |
**Important:** DEFAULT does not mean NULL, and explicitly supplying NULL does not automatically trigger the default.
6. Understanding NULL in INSERT
NULL represents a missing or unknown value. It is not the same as zero or an empty string.
### Insert a student without an email
```sql
INSERT INTO students (student_name, age, city)
VALUES ('Priya', 20, 'Kanpur');
```
The email is omitted, so it becomes NULL because the column has no default and permits NULL.
### Explicitly insert NULL
```sql
INSERT INTO students (student_name, email, age, city)
VALUES ('Dev', NULL, 21, 'Jaipur');
```
This is allowed because email is nullable.
### What if you omit a required name?
```sql
INSERT INTO students (email, age)
VALUES ('missing-name@example.com', 20);
```
This fails because student_name is NOT NULL and has no default.
### Check NULL values
```sql
SELECT student_name
FROM students
WHERE email IS NULL;
```
Use IS NULL to test for NULL. Do not write email = NULL, because ordinary comparisons with NULL evaluate to UNKNOWN.
7. RETURNING: Retrieve Inserted Values
PostgreSQL supports RETURNING, which returns values from rows inserted by the statement.
This is useful when an identity column generates an ID that your application needs immediately.
### Return the generated ID
```sql
INSERT INTO students (student_name, email, age, city)
VALUES ('Ishita', 'ishita@example.com', 22, 'Lucknow')
RETURNING student_id;
```
The returned student_id is the actual ID assigned by the database. Its exact value depends on the existing data and identity sequence.
### Return multiple columns
```sql
INSERT INTO students (student_name, email, age, city)
VALUES ('Rohan', 'rohan@example.com', 23, 'Noida')
RETURNING student_id, student_name, email, created_at;
```
This returns the newly inserted row's requested values.
### Return all columns
```sql
INSERT INTO students (student_name, email, age, city)
VALUES ('Ananya', 'ananya@example.com', 21, 'Delhi')
RETURNING *;
```
RETURNING * can be convenient during development. In application code, return only the columns needed by the caller.
### Important notes
- RETURNING is a PostgreSQL feature and is also supported by some other database systems in their own forms.
- It avoids a separate query just to retrieve values from the inserted row.
- It does not commit the transaction by itself.
- If the INSERT fails, it does not return a successful inserted row.
Insert a student and return the generated ID
The ID is generated by PostgreSQL. The placeholder represents the actual ID returned by the database, not a literal SQL value.
8. INSERT INTO ... SELECT
INSERT can take rows from another query instead of using a VALUES list.
### Example: Create an archive table
```sql
CREATE TABLE adult_student_archive (
student_id INTEGER,
student_name VARCHAR(100),
email VARCHAR(150),
age INTEGER,
city VARCHAR(80)
);
```
### Copy selected rows
```sql
INSERT INTO adult_student_archive (
student_id,
student_name,
email,
age,
city
)
SELECT
student_id,
student_name,
email,
age,
city
FROM students
WHERE age >= 21;
```
This inserts the selected rows into the archive table.
### Verify
```sql
SELECT student_id, student_name, age
FROM adult_student_archive
ORDER BY student_id;
```
The inserted rows depend on the contents of students at the time the INSERT ... SELECT statement runs.
### Important considerations
- The selected column count must match the target column count.
- Corresponding data types must be compatible.
- Target constraints still apply.
- The source query can use joins, filtering, grouping, and other SELECT features.
- Re-running this example can insert duplicate records because the archive table has no uniqueness constraint.
If duplicate prevention is required, define an appropriate key or uniqueness rule and design the insertion accordingly.
9. Constraints and INSERT Errors
Database constraints protect the integrity of your data.
### NOT NULL violation
```sql
INSERT INTO students (student_name)
VALUES (NULL);
```
This fails because student_name is NOT NULL.
### UNIQUE violation
```sql
INSERT INTO students (student_name, email)
VALUES ('Another Asha', 'asha@example.com');
```
If that email already exists, PostgreSQL rejects the insert because email has a UNIQUE constraint.
### CHECK violation
```sql
INSERT INTO students (student_name, age)
VALUES ('Young Student', 12);
```
This fails because the age check requires a non-NULL age to be at least 16.
### Primary-key violation
If you explicitly insert a student_id that already exists, the primary-key constraint rejects the duplicate.
### Foreign-key violation
If a table references another table through a foreign key, an inserted non-NULL reference must satisfy that constraint unless the constraint's definition or timing allows otherwise.
### A useful debugging method
1. Read the database error carefully.
2. Identify the table and constraint involved.
3. Check the values being inserted.
4. Correct the data or the intended business rule.
5. Retry the operation safely.
Do not remove a constraint merely to make an INSERT succeed. First determine whether the data or the rule is wrong.
10. INSERT with Transactions
A transaction lets you group database operations into a unit of work.
### Basic transaction
```sql
BEGIN;
INSERT INTO students (student_name, email, age, city)
VALUES ('Nisha', 'nisha@example.com', 22, 'Lucknow');
COMMIT;
```
COMMIT makes the transaction's changes permanent, subject to the database's durability guarantees.
### Roll back instead
```sql
BEGIN;
INSERT INTO students (student_name, email, age, city)
VALUES ('Temporary Student', 'temporary@example.com', 20, 'Delhi');
ROLLBACK;
```
ROLLBACK discards the transaction's uncommitted changes.
### Why use a transaction?
Transactions are useful when multiple operations must succeed or fail together.
For example, a checkout workflow may insert an order and its order items. If an operation fails, the application can roll back the transaction rather than leave only part of the intended database change.
### Important notes
- In PostgreSQL, statements outside an explicit transaction block normally run in their own transaction.
- A successful INSERT inside an explicit transaction may still be rolled back before COMMIT.
- Identity or sequence values may be consumed even if the transaction rolls back. Gaps in generated IDs are normal.
- Applications should handle errors and transaction boundaries deliberately.
11. Safe INSERT Practices in Applications
When an application inserts user-provided data, use parameterized queries rather than concatenating strings into SQL.
### Unsafe pattern
```javascript
const sql =
"INSERT INTO students (student_name) VALUES ('" +
userName +
"')";
```
If userName contains quotes or malicious SQL, string construction can cause incorrect queries or SQL injection vulnerabilities.
### Safer pattern with a parameterized query
For example, with a PostgreSQL Node.js client such as node-postgres:
```javascript
const result = await pool.query(
"INSERT INTO students (student_name, email) VALUES ($1, $2) RETURNING student_id, student_name",
[userName, userEmail]
);
const insertedStudent = result.rows[0];
```
The values are passed separately from the SQL statement. This is the normal safe approach for values in parameterized queries.
### Other practices
- Validate input according to the application's business rules.
- Keep database constraints even when application validation exists.
- Handle unique-constraint and other database errors appropriately.
- Use transactions when several related writes must succeed together.
- Avoid returning unnecessary personal or sensitive data.
- Use an appropriate batch size for large imports.
- Do not assume that an INSERT has committed merely because RETURNING produced a row.
12. Guided Practice
Use the students table defined earlier.
### Exercise 1: Insert one student
Insert a student named Tara, age 20, living in Lucknow, with email tara@example.com.
### Exercise 2: Insert three students
Insert Aman, Simran, and Vivek using one multi-row INSERT. Give each a unique email and a valid age.
### Exercise 3: Use the default city
Insert a student named Kunal with a unique email and age, but omit city.
### Exercise 4: Insert NULL
Insert a student with a unique name and age but no email. Verify that email is NULL.
### Exercise 5: Return an ID
Insert a student and use RETURNING to retrieve student_id and student_name.
### Exercise 6: Insert selected rows
Insert all students aged 21 or older into a separate table containing compatible columns.
### Exercise 7: Diagnose an error
Explain why inserting an age of 10 fails.
### Exercise 8: Understand duplicate emails
Explain what happens when you insert an email already present in the table.
### Exercise 9: Transaction control
Write a transaction that inserts a student and then rolls back the change.
### Exercise 10: Application security
Explain why parameterized SQL is safer than concatenating a user-supplied name into an INSERT statement.
13. Practice Solutions
### Solution 1: Insert one student
```sql
INSERT INTO students (student_name, email, age, city)
VALUES ('Tara', 'tara@example.com', 20, 'Lucknow');
```
### Solution 2: Insert three students
```sql
INSERT INTO students (student_name, email, age, city)
VALUES
('Aman', 'aman@example.com', 21, 'Noida'),
('Simran', 'simran@example.com', 22, 'Delhi'),
('Vivek', 'vivek@example.com', 23, 'Lucknow');
```
### Solution 3: Use the default city
```sql
INSERT INTO students (student_name, email, age)
VALUES ('Kunal', 'kunal@example.com', 20);
```
The city is 'Unknown'.
### Solution 4: Insert NULL
```sql
INSERT INTO students (student_name, age)
VALUES ('Riya', 20);
SELECT student_name, email
FROM students
WHERE student_name = 'Riya'
AND email IS NULL;
```
The query finds the inserted row if the assumed name is unique in the practice data.
### Solution 5: Return an ID
```sql
INSERT INTO students (student_name, email, age)
VALUES ('Sana', 'sana@example.com', 22)
RETURNING student_id, student_name;
```
PostgreSQL returns the generated ID and name.
### Solution 6: Insert selected rows
Assuming adult_student_archive exists:
```sql
INSERT INTO adult_student_archive (
student_id,
student_name,
email,
age,
city
)
SELECT
student_id,
student_name,
email,
age,
city
FROM students
WHERE age >= 21;
```
Re-running this query can insert the same source rows again unless duplicates are prevented.
### Solution 7: Diagnose an age error
The CHECK constraint requires age >= 16. An age of 10 violates that rule.
### Solution 8: Duplicate email
The UNIQUE constraint rejects the insert if another row already has that non-NULL email.
### Solution 9: Transaction rollback
```sql
BEGIN;
INSERT INTO students (student_name, email, age)
VALUES ('Rollback Student', 'rollback@example.com', 20);
ROLLBACK;
```
The inserted row is not retained as a committed change.
### Solution 10: Parameterized SQL
Parameterized queries separate SQL structure from supplied values, reducing SQL injection risk and handling special characters correctly.
14. Knowledge Check: MCQs
Choose the best answer for each question.
15. SQL Interview Questions and Answers
### 1. What is INSERT in SQL?
INSERT is a DML statement used to add new rows to a table.
### 2. What is the basic INSERT syntax?
INSERT INTO table_name (column1, column2) VALUES (value1, value2).
### 3. Why specify column names?
It documents the intended mapping between values and columns and makes the query less dependent on the table's complete column order.
### 4. Can you insert multiple rows in one statement?
Yes. Use multiple parenthesized value groups after VALUES.
### 5. What happens when a column is omitted?
Its default is used when defined. Otherwise, NULL is used if permitted; required columns without a usable default can cause the insert to fail.
### 6. What is the difference between DEFAULT and NULL?
DEFAULT requests the declared default expression. NULL explicitly supplies a missing or unknown value.
### 7. What is RETURNING in PostgreSQL?
RETURNING retrieves values from rows inserted or otherwise affected by a supported DML statement.
### 8. How do you retrieve a generated primary key?
Use INSERT ... RETURNING primary_key_column.
### 9. Can INSERT use a SELECT statement?
Yes. INSERT INTO ... SELECT inserts rows produced by a query.
### 10. What is a UNIQUE constraint?
It prevents duplicate values for the constrained column or combination of columns, subject to the database's NULL semantics.
### 11. What happens when NOT NULL is violated?
The database rejects the insert unless the value is supplied or a suitable default is applied instead.
### 12. What is a CHECK constraint?
It enforces a condition on inserted or updated values. A CHECK expression that evaluates to UNKNOWN because of NULL does not fail by itself, so pair it with NOT NULL when non-NULL data is required.
### 13. What is the purpose of a transaction?
It groups operations so they can be committed together or rolled back before commit.
### 14. Does RETURNING mean the transaction is committed?
No. It returns values from the statement, but an explicit transaction still requires COMMIT to make its changes permanent.
### 15. Why can identity values contain gaps?
Identity and sequence allocation is not gap-free. Values can be consumed by failed or rolled-back operations.
### 16. What is a bulk INSERT?
It inserts multiple records in a single statement or batch. For large PostgreSQL imports, COPY may be more suitable.
### 17. How can you avoid SQL injection during insertion?
Use parameterized queries, validate inputs, and avoid concatenating untrusted values into SQL text.
### 18. What happens when an INSERT violates a foreign key?
The database normally rejects the row if its referenced key does not satisfy the foreign-key constraint.
### 19. How can you insert only rows not already present?
Use a suitable uniqueness constraint and an appropriate conflict-handling strategy, or a carefully designed anti-match query. The correct choice depends on concurrency and the desired duplicate policy.
### 20. What is PostgreSQL's ON CONFLICT clause?
It supports conflict handling for applicable unique or exclusion constraints, such as DO NOTHING or DO UPDATE. Use it deliberately rather than treating every insert error as a duplicate.
### 21. What does INSERT ... RETURNING * return?
It returns all columns of the rows inserted by the statement, including generated and defaulted values.
### 22. Is INSERT always faster as one multi-row statement?
Not in every workload. A multi-row statement can reduce round trips, but performance depends on batch size, constraints, triggers, transaction strategy, and the client.
### 23. What is the difference between INSERT and UPDATE?
INSERT adds new rows. UPDATE changes values in existing rows that match its condition.
### 24. Why should you avoid assuming generated IDs are sequential without gaps?
Sequence values can be consumed even when a transaction fails or rolls back. They are identifiers, not guaranteed row counts.
### 25. How do you verify an inserted record?
Use RETURNING to inspect values immediately, or issue a SELECT with an appropriate key or unique condition.
16. Summary and Final Checklist
## Summary
- INSERT adds rows to an existing table.
- Specify the target columns explicitly.
- Use multiple VALUES groups to insert several rows in one statement.
- Understand how omitted columns, DEFAULT, and NULL differ.
- Use RETURNING in PostgreSQL to retrieve generated IDs and inserted values.
- INSERT INTO ... SELECT copies query results into another table.
- Constraints protect data integrity and can reject invalid records.
- Transactions let you commit or roll back groups of related changes.
- Parameterized queries help protect application code from SQL injection.
- Sequence and identity values are not guaranteed to be gap-free.
## Before moving to the next lesson
- [ ] Write a single-row INSERT.
- [ ] Insert multiple rows in one statement.
- [ ] Explain what happens when a column is omitted.
- [ ] Distinguish DEFAULT from NULL.
- [ ] Retrieve a generated ID with RETURNING.
- [ ] Insert rows using INSERT INTO ... SELECT.
- [ ] Explain a UNIQUE constraint violation.
- [ ] Use BEGIN, COMMIT, and ROLLBACK.
- [ ] Explain why parameterized queries are safer.
- [ ] Explain why generated IDs can have gaps.
**Next:** Continue to the next lesson in the Modifying Data chapter.
17. Common Mistakes
Avoid these common errors when inserting data.
Common pitfalls to avoid
Real debugging lessons and exact syntax contrasts to avoid runtime bugs
#1 Omitting the target column list
Why it happens: The query relies on the table's full column order.
Key takeaway: Explicit target columns make the query clearer and let defaults and generated columns work as intended.
#2 Confusing NULL with DEFAULT
Why it happens: The author expects an explicitly supplied NULL to trigger the default.
Key takeaway: DEFAULT requests the declared default. Explicit NULL does not trigger it.
#3 Building SQL by concatenating user input
Why it happens: Untrusted input is mixed directly into SQL syntax.
Key takeaway: Parameterized queries separate values from SQL syntax and help prevent SQL injection.
Ready for hands-on challenges?
Test your skills with shuffled coding challenges in the Practice Arena.
Key takeaways
- INSERT adds new rows to a table.
- Always specify the target columns unless deliberately relying on the complete table column order.
- The number of values must match the number of specified columns.
- Omitted columns receive their declared default or NULL when permitted and when no default is defined.
- DEFAULT requests a column's declared default; NULL represents a missing or unknown value where permitted.
- Multi-row INSERT can add several records in one statement.
- PostgreSQL RETURNING can return generated IDs and other inserted values.
- Primary keys, UNIQUE, NOT NULL, CHECK, and foreign-key constraints can reject invalid inserts.
- INSERT INTO ... SELECT inserts the result of another query.
- Use parameterized queries in applications instead of building SQL by concatenating untrusted input.
- A successful INSERT does not necessarily mean the transaction has been committed.