INSERT: one row, many rows, from a SELECT, with RETURNING

Part of the free LogicWiz SQL & Databases course, Module 8: Changing data. The queries and practice run in the app on real Postgres.

A new restaurant joins the app on Diwali with four dishes. The app must save all of them, and then show the new order screen with the order id the database just created. That is three kinds of INSERT.

You'll learn to

Insert one or many rows, always naming the columns
Copy rows from a query with INSERT … SELECT
Get back what was inserted with RETURNING
Know what DEFAULT and NULL fill in

Learn

One row, columns named

INSERT INTO restaurants (restaurant_id, name, city, cuisine, rating, opened_on)
VALUES (9, 'Truffles', 'Bengaluru', 'Cafe', NULL, '2025-07-15');

SELECT * FROM restaurants WHERE restaurant_id = 9;

The column list says which value goes where. The values must match it in order and type. NULL says "no rating yet".

Many rows in one statement

INSERT INTO menu_items (item_id, restaurant_id, name, category, price, is_veg) VALUES
  (19, 8, 'Garlic Bread', 'Starter', 149, true),
  (20, 8, 'Farmhouse Pizza', 'Main', 399, true),
  (21, 8, 'Choco Lava Cake', 'Dessert', 109, true);

SELECT item_id, name, price FROM menu_items WHERE restaurant_id = 8;

One INSERT, many rows, separated by commas. It is faster than three INSERTs (one trip to the database) and it is all-or-nothing: if one row is bad, none are saved.

INSERT INTO customers VALUES (…) with no column list works today, and breaks the day someone adds a column or reorders the table. Worse, if two columns have the same type, values can land in the wrong column with no error. Always name the columns.

Columns you leave out

INSERT INTO customers (customer_id, name, city, signup_date)
VALUES (13, 'Neha Verma', 'Pune', '2025-07-15');

SELECT customer_id, name, is_premium, referred_by
FROM customers
WHERE customer_id = 13;

A column left out gets its DEFAULT: is_premium has DEFAULT false. A column with no default gets NULL, like referred_by. If that column were NOT NULL with no default, the INSERT would fail.

INSERT … SELECT: copy rows from a query

CREATE TABLE diwali_campaign (
  customer_id  INT PRIMARY KEY,
  name         VARCHAR(40) NOT NULL,
  city         VARCHAR(30) NOT NULL
);

INSERT INTO diwali_campaign (customer_id, name, city)
SELECT customer_id, name, city
FROM customers
WHERE is_premium;

SELECT * FROM diwali_campaign ORDER BY customer_id;

No VALUES: the SELECT produces the rows, and they go straight in. Columns are matched by position, not by name, so keep both lists in the same order.

RETURNING: see what you just inserted

INSERT INTO orders (order_id, customer_id, restaurant_id, ordered_at, status)
VALUES (1025, 4, 3, '2025-07-13 20:00', 'pending')
RETURNING order_id, status, delivery_fee;

RETURNING turns the INSERT into something that also returns rows, here including delivery_fee, which the DEFAULT filled with 0. Apps use it to get a generated id back without a second query.

A bad row stops the whole INSERT

INSERT INTO orders (order_id, customer_id, restaurant_id, ordered_at, status) VALUES
  (1025, 4, 3, '2025-07-13 20:00', 'pending'),
  (1026, 99, 3, '2025-07-13 20:05', 'pending');

Customer 99 does not exist, so the foreign key refuses row 1026, and order 1025 is not saved either.

MySQL has no RETURNING: call LAST_INSERT_ID() after the INSERT to get an AUTO_INCREMENT id. INSERT IGNORE skips bad rows instead of failing (and hides real errors, so use it with care). Multi-row VALUES and INSERT … SELECT work the same.

Interviews ask: "How do you copy rows from one table to another?" Answer INSERT INTO target (cols) SELECT … FROM source WHERE … when the table exists, and CREATE TABLE new AS SELECT … to create and fill a new one in one step.

In one line

INSERT INTO t (cols) VALUES (…), (…) or INSERT INTO t (cols) SELECT …; name the columns, and add RETURNING to get the new rows back.

Interview corner

How do you copy data from one table to another?

Into an existing table: INSERT INTO target (a, b) SELECT a, b FROM source WHERE …. To create a new table from a query: CREATE TABLE target AS SELECT … (SQL Server/MySQL also have SELECT … INTO). Columns are matched by position, so list them in the same order.

How do you get the id of a row you just inserted?

In Postgres, INSERT … RETURNING id returns it from the same statement. In MySQL, call LAST_INSERT_ID() after the insert; in SQL Server, OUTPUT inserted.id or SCOPE_IDENTITY(). Never use SELECT MAX(id): another session may have inserted in between.

What is the fastest way to insert many rows?

One multi-row INSERT (or INSERT … SELECT) instead of many single-row ones, inside one transaction, so there is one round trip and one commit. For very large loads, Postgres COPY (MySQL LOAD DATA INFILE) is faster still.