PostgreSQL Lesson 16 – INSERT Data | Dataplexa

Inserting Data

You have built tables, defined data types, and applied constraints. Now it is time to put data in. The INSERT statement is how every single row enters a PostgreSQL table — whether it is one new customer, a bulk load of thousands of products, or data copied from another table. This lesson covers every form of INSERT you will use in real projects, with examples from the Dataplexa Store dataset.

Basic INSERT — One Row at a Time

The simplest INSERT specifies the table name, lists the columns you are providing values for, and gives matching values in the same order. You do not have to list every column — columns with defaults or that allow NULL can be omitted.

-- Insert a single customer
INSERT INTO customers (first_name, last_name, email, city, state)
VALUES ('Alice', 'Morgan', 'alice.morgan@example.com', 'New York', 'NY');

-- Insert a product — stock_qty and is_active use their defaults
INSERT INTO products (name, category, price)
VALUES ('Wireless Mouse', 'Electronics', 29.99);

-- Insert an employee with all fields specified
INSERT INTO employees (first_name, last_name, email, department, job_title, salary)
VALUES ('James', 'Carter', 'james.carter@dataplexa.com', 'Sales', 'Account Manager', 58000.00);

-- Verify the inserts
SELECT * FROM customers ORDER BY id DESC LIMIT 1;
SELECT * FROM products  ORDER BY id DESC LIMIT 1;

Column Order Matters — and How to Be Safe

The values in your VALUES list must match the column list exactly — same count, same order, same compatible types. If you list the columns explicitly (which you should always do), the order of columns in the table definition does not matter. Only the order you write in the INSERT statement matters.

-- This works — columns listed in any order, values match
INSERT INTO customers (email, first_name, last_name, state, city)
VALUES ('bob.smith@example.com', 'Bob', 'Smith', 'CA', 'Los Angeles');

-- This is risky — no column list, values must match table definition order exactly
INSERT INTO customers
VALUES (DEFAULT, 'Carol', 'Davis', 'carol.davis@example.com', 'Houston', 'TX', DEFAULT);
-- DEFAULT fills in id (SERIAL) and joined_date

-- Always list your columns explicitly — safer, clearer, won't break if columns are added later
INSERT INTO customers (first_name, last_name, email, city, state)
VALUES ('David', 'Lee', 'david.lee@example.com', 'Chicago', 'IL');

INSERT 0 1
INSERT 0 1
INSERT 0 1

Inserting Multiple Rows at Once

You can insert multiple rows in a single INSERT statement by providing multiple value groups separated by commas. This is far more efficient than running separate INSERT statements in a loop — PostgreSQL processes it as one operation instead of many round trips.

-- Insert multiple products in one statement
INSERT INTO products (name, category, price, stock_qty) VALUES
  ('Standing Desk',    'Furniture',    349.99,  15),
  ('Monitor Arm',      'Accessories',   49.99,  42),
  ('Webcam HD',        'Electronics',   79.99,  28),
  ('Desk Lamp',        'Office',        34.99,  60),
  ('USB Hub',          'Electronics',   24.99, 100),
  ('Ergonomic Chair',  'Furniture',    299.99,   8);

-- Insert multiple customers
INSERT INTO customers (first_name, last_name, email, city, state) VALUES
  ('Emma',  'Wilson', 'emma.wilson@example.com',  'Phoenix',      'AZ'),
  ('Liam',  'Brown',  'liam.brown@example.com',   'Philadelphia', 'PA'),
  ('Olivia','Jones',  'olivia.jones@example.com', 'San Antonio',  'TX');

SELECT COUNT(*) FROM products;

INSERT 0 6
INSERT 0 3

count
-------
36

INSERT with RETURNING — Getting Values Back Immediately

After an INSERT, PostgreSQL normally just tells you how many rows were inserted. But often you need the auto-generated ID or a default value that was just created. The RETURNING clause lets you retrieve column values from the rows you just inserted — without needing a separate SELECT query.

-- Get the new customer's auto-generated ID immediately after insert
INSERT INTO customers (first_name, last_name, email, city, state)
VALUES ('Noah', 'Martinez', 'noah.martinez@example.com', 'Dallas', 'TX')
RETURNING id;

-- Return multiple columns including the timestamp default
INSERT INTO orders (customer_id, total_amount, status)
VALUES (1, 149.97, 'pending')
RETURNING id, order_date, status;

-- Return the full inserted row
INSERT INTO employees (first_name, last_name, email, department, salary)
VALUES ('Sofia', 'Taylor', 'sofia.taylor@dataplexa.com', 'Engineering', 92000.00)
RETURNING *;

INSERT INTO … SELECT — Copying Data Between Tables

You can populate a table with data from an existing table or query using INSERT INTO … SELECT. No VALUES clause needed — the SELECT provides the rows. This is how you copy data between tables, populate summary tables, create backups, or move data from a staging table into a production table.

-- Create a table to hold high-value customers
CREATE TABLE vip_customers (
  id         INTEGER PRIMARY KEY,
  first_name VARCHAR(50),
  last_name  VARCHAR(50),
  email      VARCHAR(100)
);

-- Populate it from customers who have orders totalling over $500
INSERT INTO vip_customers (id, first_name, last_name, email)
SELECT c.id, c.first_name, c.last_name, c.email
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name, c.last_name, c.email
HAVING SUM(o.total_amount) > 500;

SELECT COUNT(*) FROM vip_customers;

CREATE TABLE
INSERT 0 18

count
-------
18

ON CONFLICT — Handling Duplicate Inserts Gracefully

In real applications, INSERT operations sometimes attempt to insert a row that would violate a UNIQUE or PRIMARY KEY constraint — a duplicate email, a duplicate product SKU, or an ID that already exists. Instead of letting the error crash your application, PostgreSQL's ON CONFLICT clause lets you handle the situation gracefully — either skip the duplicate silently or update the existing row with the new values.

-- DO NOTHING — skip silently if email already exists
INSERT INTO customers (first_name, last_name, email, city, state)
VALUES ('Alice', 'Morgan', 'alice.morgan@example.com', 'Boston', 'MA')
ON CONFLICT (email) DO NOTHING;

-- DO UPDATE — update the existing row if email already exists (upsert)
-- EXCLUDED refers to the row that was rejected
INSERT INTO customers (first_name, last_name, email, city, state)
VALUES ('Alice', 'Morgan-Updated', 'alice.morgan@example.com', 'Boston', 'MA')
ON CONFLICT (email) DO UPDATE
  SET last_name = EXCLUDED.last_name,
      city      = EXCLUDED.city,
      state     = EXCLUDED.state;

-- Upsert a product — update price and stock if SKU already exists
INSERT INTO products (name, category, price, stock_qty)
VALUES ('Wireless Mouse', 'Electronics', 27.99, 50)
ON CONFLICT (name) DO UPDATE
  SET price     = EXCLUDED.price,
      stock_qty = EXCLUDED.stock_qty;

SELECT last_name, city FROM customers WHERE email = 'alice.morgan@example.com';

INSERT 0 0 -- skipped silently
INSERT 0 1 -- updated existing row
INSERT 0 1 -- updated existing product

last_name | city
---------------+--------
Morgan-Updated | Boston

This pattern — insert if new, update if exists — is called an upsert. It is extremely common in data pipelines, API integrations, and sync operations where you do not always know whether a record already exists.

INSERT Best Practices

Always list column names explicitly in your INSERT statements. Never rely on positional order — if a column is added or reordered in the table, positional inserts break silently with wrong data going into the wrong columns. Use multi-row INSERT for bulk operations instead of looping single-row inserts — it is dramatically faster. Use RETURNING when you need the generated ID immediately rather than running a second SELECT. Use ON CONFLICT to build resilient upsert logic instead of checking for existence first with a separate query.

INSERT Quick Reference

Pattern	Use When
INSERT INTO t (cols) VALUES (...)	Inserting a single row
INSERT INTO t (cols) VALUES (...), (...)	Inserting multiple rows efficiently
INSERT ... RETURNING id	Need the generated ID or defaults back immediately
INSERT INTO t SELECT ...	Copying or transforming data from another table
ON CONFLICT DO NOTHING	Skip duplicates silently
ON CONFLICT DO UPDATE	Upsert — insert or update if duplicate

🧪 Practice Questions

Answer based on what you learned in this lesson.

1. Which clause retrieves the auto-generated ID of a row immediately after it is inserted — without a separate SELECT?

2. Which clause handles duplicate key violations during an INSERT — either skipping or updating instead of throwing an error?

3. In an ON CONFLICT DO UPDATE clause, what keyword refers to the values from the row that was rejected?

4. What INSERT syntax lets you populate a table with rows from an existing query instead of writing out VALUES?

5. What is the common name for the pattern of inserting a row if it is new, or updating it if it already exists?

Next up: Updating existing rows — changing data precisely with UPDATE and WHERE.

← Previous Course Index Next →

PostgreSQL