Skip to content
aviral gupta
Get in touch

// B1.3 · ~30 min · Beginner

Inserting rows: many at once, NULL, DEFAULT and quotes

After this lesson you can load several rows with one INSERT, leave columns empty on purpose, quote text and dates correctly, and copy rows from a query.

Lesson 3 of 4 in B1 Getting started

You will be able to

  • Insert several rows in one statement with a column list, leaving columns out (NULL) or using DEFAULT
  • Write text with '' for an apostrophe, dates as '2026-09-30' and numbers without quotes
  • Copy rows with INSERT ... SELECT and read the errors INSERT raises

  1. Warm-up · Activity 1 of 7

    Warm-up: how do you write the name O'Brien as a text value in SQL?

  2. Predict · Activity 2 of 7

    Predict before you read on: the date has no quotes, and the column is text. What does the SELECT print?

    CREATE TABLE notes (body text);
INSERT INTO notes VALUES (2026-09-30);
SELECT body FROM notes;

  3. Practice · Activity 3 of 7

    Fill in the value so that the row stores the name O'Brien.

    INSERT INTO members (id, name) VALUES (5, ____);
    INSERT INTO members (id, name) VALUES (5, );

  4. Practice · Activity 4 of 7

    Match each ERROR message to its cause.

  5. Practice · Activity 5 of 7

    What does the last statement return?

    CREATE TABLE books (id integer, title text, year integer);
INSERT INTO books VALUES (1, 'Dune', 1965), (2, 'Emma', 1815), (3, 'Kindred', 1979);
CREATE TABLE older_books (id integer, title text);
INSERT INTO older_books SELECT id, title FROM books WHERE year < 1970;
SELECT count(*) FROM older_books;

  6. Brain teaser · Activity 6 of 7

    Brain teaser. The two tables list their columns in a different order. What does the last SELECT print?

    CREATE TABLE a (title text, author text);
INSERT INTO a VALUES ('Dune', 'Frank Herbert');
CREATE TABLE b (author text, title text);
INSERT INTO b SELECT * FROM a;
SELECT author FROM b;

  7. Apply · Activity 7 of 7

    Mini-task. Create members (id integer, name text, email text, city text, joined date) and load the library’s four members in one INSERT with a column list: 1 Ada, ada@example.com, Berlin, 2025-01-15; 2 Grace, grace@example.com, Munich, 2025-03-02; 3 Linus, no e-mail, Berlin, 2026-02-10; 4 Margaret, margaret@example.com, no city, 2026-05-20. List them ordered by id and check that the missing values show as empty cells.

    Check your work against this list

Build it yourself

Read the worked example, then write the exercises. Your code runs in your browser or on your computer and is never uploaded.

Worked example

Five members, four ways to insert

The script fills members in four ways: two rows in one INSERT, a row that leaves email out of its column list, a row with DEFAULT for city, and a name with an apostrophe. Then it copies the Berlin members into a second table with INSERT ... SELECT. Run it, then add a third row to the first INSERT. On your computer, run it with psql -X -q -v ON_ERROR_STOP=1 -f main.sql.

main.sql

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

-- Several rows in one statement, with a column list.
INSERT INTO members (id, name, email, city, joined) VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02');

-- A column left out of the list gets no value: NULL.
INSERT INTO members (id, name, city, joined)
VALUES (3, 'Linus', 'Berlin', '2026-02-10');

-- DEFAULT asks for the column's default value, here NULL.
INSERT INTO members
VALUES (4, 'Margaret', 'margaret@example.com', DEFAULT, '2026-05-20');

-- An apostrophe inside text is written twice.
INSERT INTO members (id, name, city)
VALUES (5, 'Siobhan O''Brien', 'Dublin');

SELECT * FROM members ORDER BY id;

-- Copy rows with INSERT ... SELECT.
CREATE TABLE berlin_members (id integer, name text);
INSERT INTO berlin_members (id, name)
SELECT id, name FROM members WHERE city = 'Berlin';

SELECT * FROM berlin_members ORDER BY id;

Run it with

psql -X -q -v ON_ERROR_STOP=1 -f main.sql

Output

 id |      name       |        email         |  city  |   joined
----+-----------------+----------------------+--------+------------
  1 | Ada             | ada@example.com      | Berlin | 2025-01-15
  2 | Grace           | grace@example.com    | Munich | 2025-03-02
  3 | Linus           |                      | Berlin | 2026-02-10
  4 | Margaret        | margaret@example.com |        | 2026-05-20
  5 | Siobhan O'Brien |                      | Dublin |
(5 rows)

 id | name
----+-------
  1 | Ada
  3 | Linus
(2 rows)
  • NULL shows as an empty cell: Linus has no email, Margaret no city, Siobhan neither email nor joined.
  • The name is stored with one apostrophe: the doubled '' in the SQL stands for one.
  • DEFAULT gave NULL, because the table declares no other default for city.
  • INSERT ... SELECT copied the two rows whose city is Berlin, and no others.
Change it and run it

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Exercises

Exercise 1 of 2

Three reviews in one INSERT

setup.sql has created an empty table reviews (id, book_id, reviewer, stars, written). Add three reviews in one INSERT with a column list: 1, book 1, Ada, 5 stars, written 2026-09-12; 2, book 3, Siobhan O'Brien, 4 stars, written 2026-09-20; 3, book 1, Grace, 3 stars, date unknown (NULL). The SELECT then lists them.

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints
  1. Hint 1

    One INSERT INTO reviews (id, book_id, reviewer, stars, written) VALUES, then three rows in brackets, separated by commas.

  2. Hint 2

    The apostrophe in O'Brien is written twice: 'Siobhan O''Brien'.

  3. Hint 3

    A row in a multi-row VALUES needs all five values; write NULL (no quotes) or DEFAULT for the unknown date.

Show a solution

One way to solve it. Yours can look different and still pass the checks.

INSERT INTO reviews (id, book_id, reviewer, stars, written) VALUES
  (1, 1, 'Ada', 5, '2026-09-12'),
  (2, 3, 'Siobhan O''Brien', 4, '2026-09-20'),
  (3, 1, 'Grace', 3, NULL);

SELECT * FROM reviews ORDER BY id;
Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

-- setup.sql has created the empty table reviews.
-- Add the three reviews here, in one INSERT.

SELECT * FROM reviews ORDER BY id;

test.sql

-- test: The table holds three reviews
SELECT count(*) = 3 FROM reviews;

-- test: Review 2 is by Siobhan O'Brien, with one apostrophe
SELECT EXISTS (SELECT 1 FROM reviews WHERE id = 2 AND reviewer = 'Siobhan O''Brien');

-- test: Review 3 has no date (NULL), not a text such as 'NULL'
SELECT EXISTS (SELECT 1 FROM reviews WHERE id = 3 AND written IS NULL);

-- test: The script lists the three reviews
-- output:
--  id | book_id |    reviewer     | stars |  written
-- ----+---------+-----------------+-------+------------
--   1 |       1 | Ada             |     5 | 2026-09-12
--   2 |       3 | Siobhan O'Brien |     4 | 2026-09-20
--   3 |       1 | Grace           |     3 |
-- (3 rows)

setup.sql

CREATE TABLE reviews (
  id integer,
  book_id integer,
  reviewer text,
  stars integer,
  written date
);

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Exercise 2 of 2

Copy the science fiction

setup.sql has created books with six books and an empty table sci_fi (id, title, year). Fill sci_fi with one INSERT ... SELECT that copies the id, title and year of every book whose genre is 'sci-fi'. Do not type the rows in yourself: the query finds them. The SELECT then lists the copies, oldest first.

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints
  1. Hint 1

    First write the query on its own: SELECT id, title, year FROM books WHERE genre = 'sci-fi';

  2. Hint 2

    Then put INSERT INTO sci_fi (id, title, year) in front of it, without VALUES.

  3. Hint 3

    The query’s columns fill the target columns by position: id, title, year in the same order on both sides.

Show a solution

One way to solve it. Yours can look different and still pass the checks.

INSERT INTO sci_fi (id, title, year)
SELECT id, title, year FROM books WHERE genre = 'sci-fi';

SELECT * FROM sci_fi ORDER BY year;
Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

-- setup.sql has created books (6 rows) and the empty table sci_fi.
-- Copy the sci-fi books into sci_fi here.

SELECT * FROM sci_fi ORDER BY year;

test.sql

-- test: sci_fi holds the three sci-fi books
SELECT count(*) = 3 FROM sci_fi;

-- test: Every copied row has the title and year of its book
SELECT count(*) = 3 FROM sci_fi s JOIN books b ON b.id = s.id AND b.title = s.title AND b.year = s.year AND b.genre = 'sci-fi';

-- test: The script lists the copies, oldest first
-- output:
--  id |    title    | year
-- ----+-------------+------
--   1 | Dune        | 1965
--   3 | Kindred     | 1979
--   6 | Neuromancer | 1984
-- (3 rows)

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE sci_fi (
  id integer,
  title text,
  year integer
);

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Common mistakes

More values than columns

INSERT INTO members (id, name) VALUES (1, 'Ada', 'Berlin');

What psql prints

ERROR:  INSERT has more expressions than target columns

Why, and the fix

The column list names two columns, but the row has three values, and PostgreSQL does not guess where 'Berlin' goes. Add the column to the list, INSERT INTO members (id, name, city) VALUES (1, 'Ada', 'Berlin');, or drop the extra value. With too few values the message is the reverse: INSERT has more target columns than expressions.

An apostrophe that ends the text

INSERT INTO members (id, name) VALUES (5, 'O'Brien');

What psql prints

ERROR:  syntax error at or near "Brien"

Why, and the fix

The single quote in O'Brien ends the text after O, so PostgreSQL reads Brien as a stray word and stops there. Inside text, write an apostrophe as two single quotes: 'O''Brien'. It is stored with one.

A date without quotes

INSERT INTO members (id, name, joined) VALUES (1, 'Ada', 2025-01-15);

What psql prints

ERROR:  column "joined" is of type date but expression is of type integer

Why, and the fix

Without quotes, 2025-01-15 is a subtraction whose result, 2009, is an integer, and a date column does not take an integer. Quote the date: '2025-01-15'. PostgreSQL then reads the text as a date. In a text column the same mistake gives no error and stores 2009, so quote dates everywhere.

PostgreSQL in the browser: PGlite 0.5.8 (PostgreSQL 18.3), Apache-2.0 and PostgreSQL License. Licence and source

Exit ticket

5 questions, no hints. Score 80% or more to complete the lesson.

Finish every activity above to unlock the exit ticket.

Report a problem

Spotted something wrong or unclear? Say what, and it will be checked and fixed.

#

At least 20 characters.

Only if you want a reply.

Key ideas

Many rows, named columns

One INSERT can add many rows: after VALUES, put each row in brackets and separate the rows with commas. Every row needs the same number of values. A column list, such as INSERT INTO members (id, name, city), says which columns you fill and in which order; the values follow that list. A column you leave out of the list gets its default value, which is NULL, no value, unless the table declares another default (module B4 shows how). DEFAULT in place of a value asks for the same thing for one column. Without a column list, the values fill the columns from the left, and any columns left over get their default too.

Writing values PostgreSQL reads right

Text goes in single quotes. An apostrophe inside text is written as two single quotes: 'O''Brien' stores O'Brien. Dates are quoted too, best as '2026-09-30'. Without quotes, 2026-09-30 is arithmetic: 2026 minus 9 minus 30, the integer 1987. In a date column that gives an error; in a text column it quietly stores 1987. Numbers need no quotes. A quoted number such as '7' is still accepted by an integer column, because PostgreSQL converts the text, but 'seven' fails: invalid input syntax for type integer. NULL without quotes means no value; 'NULL' in quotes is the four-letter text NULL.

INSERT ... SELECT, and counting columns

In place of VALUES, an INSERT can take a query: INSERT INTO berlin_members (id, name) SELECT id, name FROM members WHERE city = 'Berlin'; copies every row the query returns, which may be none, one or many. The query's columns fill the target columns by position, not by name, so list them in the same order. When the counts do not match, INSERT names the problem: INSERT has more expressions than target columns (too many values), INSERT has more target columns than expressions (too few), or VALUES lists must all be the same length. A value of the wrong type is named with its column: column "joined" is of type date but expression is of type integer.

Sources

Last reviewed September 30, 2026