INSERT

From reading to writing

So far every query only read data. Real apps also add, change and remove rows: a new customer signs up, an order is placed, a ticket is closed. The statements that change data are INSERT, UPDATE and DELETE, together called DML (data manipulation language). In the tasks of this module you write the change and then a SELECT to show the result.

INSERT one row

INSERT INTO customers (id, name, city, email, joined)
VALUES (7, 'Meena Joshi', 'Jaipur', 'meena@example.com', '2025-04-10');

You may leave out the column list and give a value for every column in table order, but do not. If someone later adds a column or changes the order, an insert without names fails or, worse, puts values into the wrong columns without any error. Naming the columns makes the statement readable and safe.

Many rows at once

Most engines (SQLite, PostgreSQL, MySQL, SQL Server) accept several rows in one VALUES list, separated by commas. One statement with many rows is faster than many statements with one row each, because the database does the bookkeeping once.

INSERT INTO products (id, name, category, price, stock)
VALUES (9,  'Desi Ghee 1L', 'Grocery', 620, 20),
       (10, 'Masala Box',   'Kitchen', 350, 18);

Defaults, NULLs and generated ids

A column you leave out of the list gets its DEFAULT (set when the table was created) or, if it has none, NULL. If the column is NOT NULL with no default, the insert fails. In SQLite an INTEGER PRIMARY KEY you leave out is filled with the next number automatically; other engines use AUTO_INCREMENT (MySQL), IDENTITY (SQL Server) or GENERATED AS IDENTITY / SERIAL (PostgreSQL).

-- tickets: status DEFAULT 'Open', priority DEFAULT 'Normal', assigned_to has no default
INSERT INTO tickets (customer_id, subject) VALUES (4, 'Wrong size received');
-- new row: id 2 | 4 | Wrong size received | Open | Normal | NULL

PostgreSQL, MySQL and SQL Server also accept the word DEFAULT inside VALUES to ask for the default explicitly. SQLite does not (it gives a syntax error), so there you simply leave the column out. INSERT INTO t DEFAULT VALUES inserts a row made only of defaults in SQLite and PostgreSQL.

INSERT ... SELECT: copying rows

Instead of VALUES you can write a SELECT. Every row it returns is inserted. This is how teams copy old rows into an archive table, fill a summary table, or move data between tables, without the data ever leaving the database.

INSERT INTO orders_archive (id, customer_id, order_date, status, archived_on)
SELECT id, customer_id, order_date, status, '2025-04-30'
FROM orders
WHERE status = 'Delivered';
-- 4 rows copied: 101, 102, 103, 106

RETURNING: get the new rows back

After an insert, an app usually needs the generated id, for example to show "Ticket #3 created". RETURNING makes the INSERT itself return rows, like a SELECT. It works in SQLite 3.35 and later and in PostgreSQL. SQL Server writes OUTPUT INSERTED.id instead. MySQL has no RETURNING (apps call LAST_INSERT_ID()), while MariaDB supports it for INSERT and DELETE, so check your engine and version.

INSERT INTO tickets (customer_id, subject, priority)
VALUES (2, 'Refund not received', 'High')
RETURNING id, status, priority;
-- 3 | Open | High

Common mistakes

• Inserting without a column list. Always name the columns.
• Values in the wrong order for the column list, such as a city in the email column. Read both lists side by side.
• Putting text without quotes, or numbers in quotes. Write 'Pune' and 620.
• Giving an id that already exists: "UNIQUE constraint failed: customers.id". Leave the id out and let the database generate it.
• Forgetting a NOT NULL column that has no default: "NOT NULL constraint failed". Supply a value or add a default.