You've got somewhere to put the data. Now it's time to put it there. INSERT is the statement that adds rows to a table and, on the face of it, it's the simplest one in the module: table name, columns, values, done. But behind that simplicity there are decisions separating a script that survives five years from one that breaks at the first refactoring: listing or not listing the columns, inserting one row or forty in a single statement, letting the engine generate the id or forcing it (and what silent disaster the latter causes), and how to retrieve the freshly created id so you can insert the lines of the order you've just recorded.

In this lesson you'll learn the five forms of INSERT, you'll finally understand what the setval block that ends greenstore.sql does, you'll be able to diagnose at a glance each of the four errors an INSERT can give you, and you'll finish by recording a complete order with its three lines using RETURNING, which is exactly what an online shop does every time somebody clicks "Confirm purchase".

1. The syntax, and why you should always list the columns
2. Inserting several rows in a single statement
3. DEFAULT and DEFAULT VALUES
4. The identity column: omitting it, forcing it and setval
5. RETURNING: retrieving what you've just inserted
6. INSERT ... SELECT: inserting the result of a query
7. The four errors of an INSERT and how to diagnose them
8. The order of insertion when there are foreign keys
9. ON CONFLICT DO NOTHING, in passing
10. Bulk loading: COPY and \copy against INSERT
11. A complete example: adding a product and recording an order
12. Common Mistakes and Tips
13. Exercises
14. Conclusion

Adding a supplier:

That psql output deserves an explanation, because it throws everybody the first time:

Every time you run an INSERT, look at the second number. If you were expecting 5 rows and it says INSERT 0 3, something's wrong.

SQL lets you omit the column list if you give a value for all of them, in the table's exact order:

It works. And it's a time bomb, for four reasons:

The rule, with no exceptions: always list the columns. It costs twenty characters and saves you an entire class of bugs. It is, by a distance, the most profitable habit in this lesson.

With an explicit list you can also omit the columns that have a DEFAULT or allow nulls, and put them in whatever order you like:

id is generated by the identity, email is left NULL (it's nullable) and active takes its DEFAULT TRUE. Three columns filled in without writing them.

Just separate the tuples with commas:

It's the form greenstore.sql uses in its nine loading blocks, and it isn't only a matter of convenience: it's vastly faster.

An INSERT of N rows doesn't cost the same as N INSERTs of one row:

The first three costs are fixed per statement and usually dominate. In practice, inserting 1,000 rows with a single statement rather than 1,000 separate statements can be between 10 and 50 times faster, and most of that difference comes from the third point: with no explicit transaction, each separate INSERT commits to disk on its own.

Practical limits: don't write a statement with 100,000 tuples. The query text becomes enormous and so does the server's memory consumption. Batches of 500 to 5,000 rows are the usual sweet spot. And if we're talking millions, the tool is no longer INSERT but COPY (section 10).

The keyword DEFAULT can appear as a value, and it means "use this column's default value":

stock takes 0, active takes TRUE and added_date takes CURRENT_DATE. It's identical to having omitted the three columns, and in fact omitting them is preferable: it reads better. DEFAULT as a value is only useful when you build the statement from a program and find it more convenient to keep the column list fixed.

DEFAULT VALUES inserts a row with all the default values:

(The date will be that of the day you run it.) It's a rare case, but it exists: "identifier reservation" tables or initial-state tables that get filled in later.

This section finally explains the most cryptic block in greenstore.sql.

Since you haven't given an id, PostgreSQL asks the associated sequence for the next value. It's the correct thing and what you'll do 99 % of the time.

Since GreenStore declares its identities GENERATED BY DEFAULT, you can give the id by hand:

It works… and it doesn't touch the sequence. The sequence still believes the last value handed out was 6. So the next INSERT without an id:

The sequence handed out 7, which was already taken. That's the sequence drifting, and it's one of the most bewildering failures there is: the statement that fails is correct and the error points at an id you never wrote.

setval repositions the sequence. The robust form, which doesn't require knowing what the sequence is called:

Now the next INSERT with no id will ask for 8 and it'll work. And here's the explanation of the course script's nine closing lines:

greenstore.sql inserts every id by hand —so the lessons can talk about "customer 7" or "order 12" with stable, reproducible ids—, so when the load finishes the nine sequences are all at 1. Without that block, the first INSERT with no id on any table would fail with a duplicate key. That's the detail 01-06 left announced "for module 5".

Three useful functions around sequences:

How to avoid all this: declare your identities GENERATED ALWAYS AS IDENTITY (05-01) and never force them. GreenStore uses BY DEFAULT for one specific teaching reason; your schema doesn't have to.

And a detail that surprises people: sequences aren't undone by a ROLLBACK. If a transaction consumes the value 21 and then aborts, that 21 is lost for good. It's deliberate —otherwise two concurrent sessions would have to wait for each other— and it means autogenerated ids have gaps. They aren't a row counter and shouldn't be used as one.

A plain INSERT only tells you how many rows it put in. RETURNING, a PostgreSQL extension, gives you back the inserted rows, generated values included:

There you have the id the engine generated (21, the one after 20), the active the DEFAULT put in and the added_date CURRENT_DATE put in. You'd have had to go looking for all of that with a later SELECT… which, moreover, wouldn't be reliable on a system with several users: another order could have come in between your INSERT and your SELECT.

RETURNING accepts the same things a SELECT does: columns, expressions, aliases and *.

The canonical case is inserting a parent and its children: an order and its lines. Without RETURNING, the sequence would be:

1. INSERT INTO orders ...
2. SELECT MAX(id) FROM orders ← here's the flaw
3. INSERT INTO order_lines (order_id, ...) VALUES (that_id, ...)

Step 2 is a race condition: if another customer confirms their purchase between your step 1 and your step 2, MAX(id) gives you back somebody else's order and you hang your lines off it. In a shop with real traffic this isn't a theoretical possibility: it happens. RETURNING solves it at the root because it returns your row, not the table's last one.

Two warnings about that table. MySQL's LAST_INSERT_ID() is per connection, so it's safe against other users but it overwrites itself if you do two INSERTs in a row. And in SQL Server you should prefer SCOPE_IDENTITY() to @@IDENTITY: the latter returns the id generated by any scope, triggers included, which is a classic source of errors.

RETURNING works the same way in UPDATE (05-03) and in DELETE (05-04), and there it's even more useful: it lets you see exactly what you've changed or what you've deleted.

Instead of VALUES, an INSERT can be fed by a query. It's pure DML: you aren't nesting a subquery (that's module 7), you're connecting a SELECT's output to an INSERT's input.

The columns are paired by position, not by name. It's the classic trap: if the SELECT returns (name, price) and the target list says (price, name), PostgreSQL will complain about incompatible types… or it won't complain and it'll leave your data crossed over.

Before archiving the delivered orders, we take a snapshot:

14 rows, the 14 delivered orders. archived_date doesn't appear in the target list, so it takes its DEFAULT CURRENT_DATE in all of them.

Supplier 5 (EcoNordic Supplies) is inactive. Verde Atlántico (supplier 3) offers to supply the same items with an 8 % surcharge on the cost. Instead of typing four separate entries:

Four new products with one statement. Look at what the SELECT does: it transforms while it copies. It concatenates a suffix onto the name, replaces supplier_id with a constant, recomputes the cost and sets stock to zero. That's the real value of INSERT ... SELECT: it isn't copying, it's copying while transforming.

Checking the costs, rounded to two decimals: 6.00 × 1.08 = 6.48; 6.90 × 1.08 = 7.452 → 7.45; 1.20 × 1.08 = 1.296 → 1.30; 8.70 × 1.08 = 9.396 → 9.40.

Everything you know from module 2 applies here, ORDER BY and LIMIT included:

(ROW_NUMBER() is a window function from module 10; here it just numbers the ranking's rows.)

And watch out for one nuance: an ORDER BY inside an INSERT ... SELECT doesn't guarantee the physical order of the rows in the target table, because in the relational model a table is an unordered set (01-05). It only determines which rows go in when there's a LIMIT. To read them in order you'll always need an ORDER BY in the SELECT.

An INSERT can fail for four reasons, one per kind of constraint. Knowing the literal messages saves you hours.

The DETAIL shows you the complete row as it would have ended up, with the default values already applied. It's very useful: there you can see which columns filled themselves in.

The constraint's name tells you which column. If it were categories_pkey, it'd be the id: probably section 4's sequence drift.

The constraint's name identifies the rule. Here it's the rating from 1 to 5, and that's why 05-01 insisted so much on naming them: chk_reviews_rating would have been just as clear; reviews_check1 wouldn't have been.

is not present in table is the unmistakable signature of a broken FK: you've referenced something that doesn't exist.

One important detail: when a multi-row INSERT fails, no row is inserted at all. A statement is atomic: either it all goes in or nothing does. If you insert 500 tuples and number 337 violates a CHECK, the other 499 aren't saved either.

Referential integrity imposes the same order it imposed on CREATE TABLE in 05-01: parents first, children afterwards.

Try skipping it and you'll see why:

The category first, the product afterwards:

The reflexive relationships have a nuance of their own. customers.referred_by_id points at the same table, so the referrer has to exist before the referred customer. In the course script it works because the customers are ordered by id and none of them is referred by another with a higher id. If that weren't the case, you'd have two options: insert first with referred_by_id set to NULL and update it afterwards (05-03), or use a deferred constraint (DEFERRABLE INITIALLY DEFERRED), which postpones the check to the end of the transaction. That last one belongs squarely to module 9's transactional model.

If you try to insert a row that violates a UNIQUE, the INSERT fails. Sometimes what you want is for it not to fail and simply do nothing:

Zero rows inserted, zero errors. It's tremendously useful for scripts that have to be rerunnable (master data loads, development seeds).

Its big sister, ON CONFLICT ... DO UPDATE, solves the classic "insert it if it doesn't exist and update it if it does". That's the UPSERT, and it has a lesson of its own: 05-05.

For large volumes, INSERT isn't the tool. PostgreSQL has COPY, designed specifically to move data between a file and a table:

\copy is psql's equivalent command, which reads the file on your machine and sends it over the connection. It needs no special permissions and it's the one you'll use almost always:

It also works the other way round, for exporting:

An honest comparison:

The rule: tens or hundreds of rows → a multi-row INSERT. Thousands or millions → COPY. And if you need to transform while loading, the professional pattern has two steps: COPY into a staging table with no constraints, and from there INSERT ... SELECT transforming into the definitive table. That pattern will come back in 05-05 and in module 11.

Equivalents by engine: MySQL has LOAD DATA INFILE, SQL Server has BULK INSERT and the bcp utility, Oracle has SQL*Loader and external tables, and SQLite has its console's .import command. None of them is compatible with the others.

We close with what a real shop does. We're working on the freshly loaded database.

Huerta del Turia (supplier 1) brings chickpeas into the Food category:

Three columns omitted and three columns filled in by themselves: id by the identity, active and added_date by their DEFAULTs. Exactly the work 05-01 declared in the DDL.

Pau Llorens Vidal (customer 6) rings up. Óscar Peris Blasco (employee 4) takes the call. He wants two olive oils, one honey and three chamomile teas, with €4.95 of shipping.

Step 1: the header, with RETURNING.

The order is number 21. We didn't choose that number: the engine told us.

Step 2: the three lines, in a single statement.

The prices are copied from the catalogue at the moment of the sale —it's the deliberate denormalisation of unit_price (01-05)— and here that means: €12.50, €9.75 and €3.25.

Lines 48, 49 and 50, following on from the 47 that were already there.

Step 3: checking the complete order.

€44.50 of goods plus €4.95 of shipping: €49.45. The whole of module 4 applied to a row you created yourself.

What's missing and why. A real system would do those two insertions inside a single transaction, so that it's impossible to be left with a header and no lines if something fails halfway. And it would deduct the stock of the three products. You'll start using the first of those in the next lesson and you'll study it in depth in module 9; the second is an UPDATE, and it's in the next lesson too.

Remember to reload. If you've run these examples, your database no longer matches the course's: there are 21 or more products, 21 orders and 50 lines. Run greenstore.sql again before continuing.

• Omitting the column list. It works today and breaks the day somebody touches the table. Always list the columns.
• Inserting the id by hand into a BY DEFAULT identity. The sequence falls behind and the next automatic INSERT fails with duplicate key ... _pkey. Fix it with setval or, better, don't do it.
• Using SELECT MAX(id) to find out what you've just inserted. It's a race condition. Use RETURNING.
• Pairing the columns badly in an INSERT ... SELECT. They're paired by position. Read them twice.
• Inserting children before parents. is not present in table. Parents first, always.
• Believing a multi-row INSERT inserts what it can. It's atomic: if one tuple fails, none goes in.
• Confusing NULL with DEFAULT. VALUES (NULL) stores NULL (or fails on a NOT NULL); VALUES (DEFAULT) applies the default value. Omitting the column is equivalent to the latter.
• Writing decimals with a comma. 12,50 isn't a number in SQL: it's invalid input syntax for type numeric (01-03).
• Loading a million rows with INSERT. That's what COPY is for. And if you need to transform, COPY into staging and then INSERT ... SELECT.
• Copying products.price into unit_price from the table instead of fixing it. A line's price is the one at the moment of the sale; if you link it to the catalogue, tomorrow your old invoices will change.
• Tip: always look at the second number in INSERT 0 N. It's the only one telling you whether it did what you expected.
• Tip: use RETURNING even when you don't need it. Seeing the resulting row with its DEFAULTs applied is the quickest way to check the DDL does what you think.
• Tip: for rerunnable master data, ON CONFLICT DO NOTHING. It turns a fragile script into an idempotent one.

GreenStore is bringing in a supplier and two of its products. Write the necessary statements, in the correct order, meeting these requirements:

1. Add the supplier Cooperativa La Safor, from Spain, with email ventas@lasafor.es, active. Retrieve its id with RETURNING.
2. Add two of its products in a single statement, both in the Food category (id 1):
   • Organic marcona almonds 250 g, price €7.90, cost €4.20, stock 60.
   • Organic Valencia oranges 5 kg, price €11.50, cost €6.00, stock 35.
   • In both, active and added_date must be left at their default values, without writing them.
3. A query showing the new supplier with its two products and the margin on each.

Predict what happens with each of these statements on the freshly reloaded database, and if it fails, say which constraint is broken and what the message would be. Then check it.

The analysis team wants a monthly sales summary table for 2025 so they don't have to recompute it for every report.

1. Create the table monthly_sales with: month (text YYYY-MM, primary key), orders (integer, mandatory), lines (integer, mandatory), units (integer, mandatory), revenue (NUMERIC(10,2), mandatory, non-negative) and computed_date (DATE, mandatory, defaulting to today).
2. Populate it with an INSERT ... SELECT from the 2025 orders.
3. Check the result and verify that the sum of the revenue column matches 2025's product revenue.

Solution 1

Three decisions you had to get right: the order (the supplier before the products, because it's the parent of the FK), a single statement for the two products, and omitting active and added_date instead of writing them. And one detail: the supplier's id of 6 was never written anywhere; it was retrieved with RETURNING and used in the following INSERT. In a real application, that value would travel in a variable.

Solution 2

a) It fails. The quantity CHECK:

CHECK (quantity > 0): a line of zero units isn't a sale. Notice that discount did get filled with its DEFAULT 0.00 before the constraint was checked.

b) It fails. The email UNIQUE:

It's 01-05's natural-key protection. The id would be different, but the email is unique: the database prevents registering the same person twice. This is exactly the scenario 05-05's UPSERT will solve.

c) It works.

Employee 9 is created, with manager_id 2 (Andrés Company Talens, who exists), salary at NULL (the column is nullable) and city at NULL. No constraint is violated: salary isn't mandatory and its CHECK (salary >= 0) gives UNKNOWN with a null, which counts as satisfied (05-01).

d) It fails. The status CHECK:

'preparing' isn't in the domain ('pending','paid','shipped','delivered','cancelled'). Look at the DETAIL: employee_id has been left NULL (a web order) and shipping_cost has taken its DEFAULT 0.00. The row was almost right.

e) It fails. FOREIGN KEY:

On the freshly reloaded database there are only 20 orders. Order 21 is the one we created in section 11, not the one that's there now. It's a reminder of why it's worth reloading the script between blocks of exercises.

Solution 3

Ten months, 16 orders and €603.52 of product revenue in 2025. The other 4 orders and €124.43 belong to 2026: 603.52 + 124.43 = €727.95, module 4's canonical figure. It adds up.

Two observations about this table's design. The first: month as a CHAR(7) in YYYY-MM format is a natural primary key and it sorts alphabetically the same way it sorts chronologically, which is exactly why that format is used so much. The second, and more important: this table is a precomputed aggregate, that is, deliberate denormalisation (01-05, section 8). Its big problem is synchronisation: if an order dated 2025 comes in tomorrow, the table becomes stale and nobody notices. The solutions —materialized views and triggers— belong to module 10.

INSERT is the data's front door, and you've got it under control:

• The syntax INSERT INTO table (columns) VALUES (...), and the rule that admits no exceptions: always list the columns. The listless form depends on the table's physical order and breaks as soon as anybody touches it.
• The output INSERT 0 N: the 0 is a relic, the N is what matters.
• Several rows in one statement, which is what the course script does and what you should do: between 10 and 50 times faster, because the dominant cost is fixed per statement.
• DEFAULT as a value and DEFAULT VALUES for an entire default row; and the equivalence between writing DEFAULT and simply omitting the column.
• The identity column: omitting it is the norm, forcing it is legal with BY DEFAULT and makes the sequence drift, and the fix is setval(pg_get_serial_sequence(...), MAX(id)) — which is exactly what the nine closing lines of greenstore.sql do. Sequences aren't undone by a ROLLBACK: ids have gaps and they don't count rows.
• RETURNING, the extension that gives back the inserted row with its generated values, and that removes the race condition of SELECT MAX(id). Along with its equivalents by engine: LAST_INSERT_ID(), SCOPE_IDENTITY(), OUTPUT.
• INSERT ... SELECT for populating history tables and duplicating catalogues transforming while copying; the columns are paired by position.
• The four errors of an INSERT, with their literal message and their diagnosis table; and the fact that a multi-row INSERT is atomic.
• The insertion order: parents before children, always.
• COPY and \copy for bulk loading, 5 to 20 times faster, and the professional staging + INSERT ... SELECT pattern.
• And the complete case: adding a product and recording order 21 with its three lines and its €49.45 total, chaining two INSERTs with RETURNING.

You know how to create tables and fill them. What you don't know yet is how to correct. In the next lesson, The UPDATE Statement, the tone changes: up to now, a mistake of yours created one extra row you could delete; from now on, a mistake of yours can overwrite twenty correct rows and leave you with no way of knowing what was there before. You'll see the professional protocol that stops that happening —write the SELECT first, count the rows, and only then turn it into an UPDATE—, the habit of working with BEGIN and ROLLBACK as a safety net, and everything UPDATE can do: several columns at once, calculations over the previous value, updates driven by another table with FROM, and RETURNING to see exactly what you've changed.