Insert and Return
Part of the SQL Foundations section of Coddy's PostgreSQL journey. Lesson 16 of 22.
INSERT INTO adds rows. List destination columns explicitly, followed by matching VALUES. PostgreSQL RETURNING returns values from the inserted rows in the same statement.
Input table products:
id | name | category | price | stock | active | note |
|---|---|---|---|---|---|---|
1 | Map | travel | 12 | 5 | True | NULL |
2 | Notebook | books | 8 | 0 | True | Recycled |
3 | Atlas | books | 24 | 3 | False | NULL |
4 | Mug | home | 12 | 8 | True | Blue |
5 | lamp | home | 30 | 2 | False | Clearance |
6 | Mini map | travel | 6 | 10 | True | NULL |
INSERT INTO products (id, name, category, price, stock, active, note) VALUES (90, 'Card', 'books', 3, 9, TRUE, NULL) RETURNING id, name;The result contains id 90 and Card, taken from the newly inserted row. Each exercise test starts from fresh fixtures, so rerunning does not accumulate inserted rows. On a persistent database you must choose keys that do not already exist.
Use an explicit column list for INSERT and RETURNING to inspect inserted values.
Challenge
MediumInsert product id 91 named Folder in books, price 4, stock 12, active true, with no note. Return id, name and stock.
The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.
Try it yourself
-- Write your SQL here.
This lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in SQL Foundations
1Types and Casts
Convert with CastsExact Decimal DivisionBoolean ValuesCreate a Typed TableActive Price Review4Change Data Safely
Insert and ReturnUpdate Selected RowsDelete and ReturnTry Changes in a TransactionReceive and VerifyPractice on your own: PostgreSQL playground