Chapter 7: PostgreSQL Insert Data
1. What does INSERT actually do? (Teacher’s simple explanation)
INSERT is the SQL command that says: “PostgreSQL, please add one or more new rows of data into this specific table. Here’s the values for each column — respect all the rules (NOT NULL, UNIQUE, CHECK, foreign keys) I defined when I created the table.”
- It adds new data only — never changes or deletes existing rows (that’s UPDATE / DELETE).
- It can add 1 row, many rows at once, or even rows coming from another query.
- If any rule is violated → PostgreSQL throws an error and nothing gets inserted (atomicity — part of ACID).
- After successful INSERT → the table has more rows, auto-increment IDs move forward, triggers fire (if any), etc.
Official PostgreSQL 18 docs (right now) call it: INSERT — create new rows in a table
2. Basic syntax patterns (the ones you’ll use 95% of the time)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
-- Pattern 1: Insert one row, specify all columns in table order INSERT INTO table_name VALUES (value1, value2, value3, ...); -- Pattern 2: Recommended — specify column names (safer, clearer) INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); -- Pattern 3: Multiple rows in one command (very efficient) INSERT INTO table_name (col1, col2, ...) VALUES (v1a, v2a, ...), (v1b, v2b, ...), (v1c, v2c, ...); -- Pattern 4: Insert from another table/query (powerful!) INSERT INTO target_table (col1, col2, ...) SELECT colA, colB, ... FROM source_table WHERE ...; |
3. Real example — let’s fill the students table we created earlier
Assume we have this table (from previous class):
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE TABLE students ( id BIGSERIAL PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, date_of_birth DATE, gpa NUMERIC(3,2) CHECK (gpa >= 0 AND gpa <= 10.00), is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, address JSONB ); |
Now insert some real Hyderabad college students:
Single row – basic style (specify columns – best practice)
|
0 1 2 3 4 5 6 7 8 9 10 |
INSERT INTO students (first_name, last_name, email, date_of_birth, gpa, address) VALUES ('Aarav', 'Patel', 'aarav.patel@hyduni.edu', '2003-07-14', 8.95, '{"city": "Hyderabad", "pin": "500081", "area": "Kukatpally"}'); |
→ PostgreSQL replies: INSERT 0 1 (0 = no OID returned, 1 = one row inserted)
Multiple rows at once (fast & common in scripts)
|
0 1 2 3 4 5 6 7 8 9 10 11 |
INSERT INTO students (first_name, last_name, email, date_of_birth, gpa) VALUES ('Priya', 'Reddy', 'priya.reddy22@gmail.com', '2002-11-03', 9.10), ('Rahul', 'Sharma', 'rahul.sharma.2004@yahoo.com','2004-02-28', 7.85), ('Sneha', 'Kumar', 'sneha.kumar@outlook.com', '2003-05-19', 8.60); |
→ INSERT 0 3 — three rows added in one command (much faster than three separate INSERTs)
Using DEFAULTs (skip columns that have defaults)
|
0 1 2 3 4 5 6 7 |
INSERT INTO students (first_name, last_name, email, gpa) VALUES ('Vikram', 'Naik', 'vikram.naik@hyd.edu', 9.40); |
→ id auto-generated, is_active=TRUE, created_at=now(), address=NULL
4. Special & powerful INSERT variations (2026 favorites)
A. Insert + see what was inserted (RETURNING clause – super useful!)
|
0 1 2 3 4 5 6 7 8 |
INSERT INTO students (first_name, last_name, email, gpa) VALUES ('Meera', 'Iyer', 'meera.iyer@gmail.com', 9.75) RETURNING id, first_name, last_name, created_at; |
→ Returns something like:
|
0 1 2 3 4 5 6 7 8 |
id | first_name | last_name | created_at ----+------------+-----------+------------------------------ 5 | Meera | Iyer | 2026-02-13 18:45:22.123456+05:30 |
You get the auto-generated id immediately — perfect for apps!
B. Insert default values explicitly
|
0 1 2 3 4 5 6 7 |
INSERT INTO students (first_name, last_name, email, is_active) VALUES ('Kiran', 'Joshi', 'kiran.joshi@hyd.edu', DEFAULT); |
C. Insert from a query (copy data between tables)
Imagine you have an old_students table:
|
0 1 2 3 4 5 6 7 8 9 10 |
INSERT INTO students (first_name, last_name, email, gpa) SELECT name_first, name_last, email_addr, grade_point FROM old_students WHERE status = 'active' AND admission_year >= 2020; |
5. Common errors & how to fix them (real student mistakes)
| Error message snippet | What happened | Fix |
|---|---|---|
| null value in column “first_name” violates not-null constraint | Forgot to provide NOT NULL column | Add value or fix typo |
| duplicate key value violates unique constraint “students_email_key” | Tried same email twice | Use different email or check existing data |
| new row for relation “enrollments” violates check constraint … | Foreign key points to non-existing student id | Insert student first or use valid id |
| numeric field overflow or CHECK constraint “students_gpa_check” | GPA = 11.00 or -1.5 | Correct value to 0–10 range |
| syntax error at or near “,” | Missing comma or extra comma in VALUES | Count commas carefully |
6. Quick performance tip for large data (2026 reality)
For 1000+ rows → prefer:
- Multi-row INSERT (as shown above)
- Or even better: COPY command (fastest for bulk)
- Wrap in one transaction: BEGIN; … many INSERTs … COMMIT;
7. Verify your inserts (psql or pgAdmin)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
-- See all students SELECT * FROM students ORDER BY id; -- Count rows SELECT COUNT(*) FROM students; -- In pgAdmin: right-click table → View/Edit Data → All Rows |
Your mini practice right now
- Insert at least 5 students (use Indian names & Hyderabad details)
- Use RETURNING to see the new ids
- Try one with JSONB address like {“city”: “Secunderabad”, “landmark”: “Paradise”}
- Run SELECT id, first_name, email, gpa FROM students WHERE gpa >= 9.0;
Next topic?
Tell me:
- Want SELECT queries in detail (WHERE, ORDER BY, LIMIT…)?
- UPDATE and DELETE next?
- How to handle foreign keys when inserting (enrollments table)?
- Bulk insert with CSV / COPY?
Your teacher is ready — fire away! 🚀
