INSERT INTO in PostgreSQL 🚀

The INSERT INTO statement is used to add new rows to a table in PostgreSQL.

1. Basic Syntax

INSERT INTO table_name (column1, column2, ...)VALUES (value1, value2, ...);

2. Insert Single Row

INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@example.com', 25);

3. Insert Multiple Rows

INSERT INTO users (name, email, age) VALUES     ('Bob', 'bob@example.com', 30),    ('Charlie', 'charlie@example.com', 22);

4. Insert Data into Specific Columns

(Other columns will use default values)

INSERT INTO users (name, email) VALUES ('David', 'david@example.com');

5. Insert with RETURNING (Get Inserted Data)

INSERT INTO users (name, email, age) VALUES ('Eve', 'eve@example.com', 28) RETURNING *;

(Returns the newly inserted row)

6. Insert Data Using DEFAULT

INSERT INTO users (name, email, age, created_at) VALUES ('Frank', 'frank@example.com', DEFAULT, DEFAULT);

7. Insert Data from Another Table

INSERT INTO archived_users (id, name, email)SELECT id, name, email FROM users WHERE age < 25;

(Transfers data from users to archived_users)

8. Insert JSON Data

INSERT INTO products (name, details)VALUES ('Laptop', '{"brand": "Dell", "ram": "16GB", "storage": "512GB SSD"}'::jsonb);

(JSONB columns store structured JSON data)

9. Insert Data with Conflict Handling

9.1 Ignore Duplicate Entries (ON CONFLICT DO NOTHING)

INSERT INTO users (name, email) VALUES ('Grace', 'grace@example.com') ON CONFLICT (email) DO NOTHING;

(If email already exists, the insert is skipped)

9.2 Update Existing Row on Conflict (ON CONFLICT DO UPDATE)

INSERT INTO users (name, email, age) VALUES ('Henry', 'henry@example.com', 35) ON CONFLICT (email) DO UPDATE SET age = EXCLUDED.age;

(If the email exists, update the age field)

10. Insert Data Using a Subquery

INSERT INTO premium_users (id, name, email)SELECT id, name, email FROM users WHERE age > 30;

(Copies data from users into premium_users)

11. Insert Data with a Sequence

If a table has a SERIAL column (auto-incrementing primary key), use:

INSERT INTO employees (name, salary) VALUES ('Alice', 50000);

(PostgreSQL automatically generates the id value)

12. Bulk Insert Data Efficiently

For large datasets, use COPY:

COPY users(name, email, age) FROM '/path/to/file.csv' DELIMITER ',' CSV HEADER;

(Faster than multiple INSERT statements)

13. Verify Inserted Data

SELECT * FROM users;

14. Delete All Data in a Table (Reset)

TRUNCATE TABLE users RESTART IDENTITY;

(Removes all rows and resets auto-increment)

Next Steps

✅ Use SELECT to fetch data
✅ Use UPDATE to modify data
✅ Use DELETE to remove data