Lesson 6 of 15 · 18 min
Inserting rows
By the end of this lesson you can write INSERT statements with an explicit column list, insert several rows in one statement, rely on DEFAULT and NULL correctly, and fill a table from a query with INSERT … SELECT.
INSERT adds new rows to a table. The safe form names the target columns, then gives the values in the same order:
INSERT INTO products (product_id, product_name, category, price, stock)
VALUES (11, 'Saffron Threads 5g', 'Grocery', 65.00, 25);PostgreSQL matches values to columns by position in your column list, not by name or by type. Any column you leave out gets its DEFAULT, or NULL if it has no default. Every constraint is checked: a NULL in a NOT NULL column, a duplicate key, a failed CHECK or a missing parent row makes the statement fail, and none of its rows are stored. A statement either inserts everything or nothing.
You may also write INSERT INTO products VALUES (…) with no column list. Then values must follow the table's column order exactly. It's shorter, and it's a trap: the statement breaks (or worse, keeps running with values in the wrong columns) as soon as the table's columns change or someone misremembers the order. Always name the columns in code that will be reused.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons