Inserting Data into a Table

In the previous section of this book we looked at creating a database, and creating, altering, and even deleting tables. These things are all concerned with the structure, or schema, of our database. This is only half the story though; the reason for creating that structure in the first place is to set the stage for how we can store data, in what format we can store data, and the format we can expect when we try to retrieve data.

Although we've mentioned data a lot, and the idea of data has been in the background of everything we've talked about so far, we've not yet spoken in detail about what we actually mean by data in the context of a database. In this section we're going to focus on that 'data' piece of the puzzle, and explore some of the various ways that we can use Data Manipulation Language (DML) to add, query, change, and remove data. We've mentioned DML before, and you may already have some idea of what it means and what we can do with it. Before we start working with it, let's just define it a little more clearly.

Data and DML

DML is a sub-language of SQL which incorporates the various key words, clauses and syntax used to write Data Manipulation Statements.

Data Manipulation Statements are used for accessing and manipulating data in the database. Data Manipulation Statements can be categorized into four different types :

  • INSERT statements - These add new data into a database table.
  • SELECT statements - Also referred to as Queries; these retrieve existing data from database tables. We've worked with this type a bit already.
  • UPDATE statements - These update existing data in a database table.
  • DELETE statements - These delete existing data from a database table.

We'll be working with all of these types of statements in this and the following chapters. The actions performed by these four types of statement are sometimes also referred to as CRUD operations.

A Bit About CRUD

The term CRUD is a commonly used acronym in the database world. The letters in CRUD stand for the words CREATE, READ, UPDATE, and DELETE. These four words are analogous to our INSERT, SELECT, UPDATE and DELETE statements, and we can think of these statements as performing their equivalent CRUD operations. Web applications whose main purpose is to provide an interface to perform these operations are often referred to as 'CRUD apps'.

The first of these operations we'll look at is creating, or adding, data. Before we do that though, we need to put back the table that we removed at the end of the previous chapter.


First of all, make sure that you're connected to the sql_book database via the psql console. Your command prompt should look like this:


Now execute the following SQL statement:

    id serial UNIQUE NOT NULL,
    full_name character varying(25) NOT NULL,
    enabled boolean DEFAULT true,
    last_login timestamp without time zone DEFAULT now()

You should receive the CREATE TABLE response, and a prompt ready to receive the next statement:


Thus, we've got our users table back but it's currently empty of data.

Empty Table

If we execute a SQL statement to retrieve all of the data in the table, the response tells us that there are (0 rows):

SELECT * FROM users;
 id | full_name | enabled | last_login
(0 rows)

We'll talk a bit more about what we mean by rows shortly, what this basically means though is that our table has no data in it. Let's change that.

Insertion Statement Syntax

Here is the general form of an INSERT SQL statement:

INSERT INTO table_name
            (column1_name, column2_name,...)
     VALUES (data_for_column1, data_for_column2, ...);

The ellipses in the above command format indicate that there can be additional names and values. The INSERT command allows one or more column names and values.

When using an INSERT statement, we have to provide three key pieces of information:

  1. The table name we wish to store data in.
  2. The names of the columns we're adding data to.
  3. The values we wish to store in the columns listed directly after the table name.

When inserting data into a table, you may specify all the columns from the table, just a few of them, or none at all. Depending on how your table is structured, and how your data row is ordered, not specifying columns can sometimes lead to unexpected results or errors, so it is generally best to specify which columns you want to insert data into.

When specifying columns, for each column specified you must supply a value for it in the VALUES clause, otherwise you'll get an error back. If you don't specify a column for data insertion, then null or a default value will be added to the record you wish to store instead.

Adding Rows of Data


If we think of columns as giving structure to our table, then we can think of rows (sometimes referred to as 'tuples') as actually containing the data. Each row in a table is an individual entity, which can perhaps be seen as the logical equivalent of a record, and has a corresponding value for each column in the table. The rows and columns work together. It is the intersection of the structure provided by our columns and the data in our rows that create the structured data that we need.

Adding a Single Row

Let's revisit what our table looks like before we add the data, so we know what columns we have and what type of data we need to add to it. The structure of the table is also referred to as the schema of a table.

\d users
                   Table "public.users"
   Column   |            Type             |   Modifiers
 id         | integer                     | not null
 full_name  | character varying(25)       | not null
 enabled    | boolean                     | default true
 last_login | timestamp without time zone | default now()

Now we want to add a record, or row, into the users table with the following values:

  • id: We'll use the default value for this; an integer generated by the nextval function
  • full_name: 'John Smith'
  • enabled: false
  • last_login: We'll use the default value for this; The current time and date.

First let's try to add this row of data to users without specifying the columns by running the INSERT statement below.

INSERT INTO users VALUES ('John Smith', false);

If you execute this statement you should receive the following error:

ERROR:  invalid input syntax for integer: "John Smith"
LINE 1: INSERT INTO users VALUES ('John Smith', false);

This is basically telling us that we are trying to insert an invalid value, the string "John Smith", into an integer column. The reason for this is the values we've included in our INSERT statement don't match up with the defined order of the columns in our users table. PostgreSQL thinks we wanted to insert "John Smith" into our id column, which has a type of integer, since this is the first column in our table and "John Smith" was the first value in our VALUES list.

There's a couple of things we could do here.

  1. We could use the keyword DEFAULT as the 'value' for our id column in our VALUES list. This would indicate that we want PostgreSQL to use the nextval function that we've set as the default for this column. Note that we wouldn't need to use DEFAULT for last_login; for any columns that we omit, PostgreSQL will either use the default (if one has been set) or set the column to NULL.

  2. We could specify the columns in our INSERT statement (ensuring that the order of those columns matches up with our values).

Let's take this second approach.

INSERT INTO users (full_name, enabled)
           VALUES ('John Smith', false);

Note that the order of the columns must match the order of the values to be inserted, but by specifying both the columns and the values, it is much easier to ensure that the order matches up correctly. Looking at our statement above, we can clearly see that the columns we specified match up with the data that we want to insert.

This time our INSERT statement should be executed successfully, and we should get the following command tag back in response:


The first digit after INSERT in this tag is the oid, which we won't cover in this book. The second digit is the count of rows that were inserted; since we inserted one row, the count is 1.

Adding Multiple Rows

If you're adding lots of data, you probably won't want to execute a separate INSERT statement for each row. Fortunately we can use a single INSERT statement to add multiple rows of data to a table. Let's add in two more records to our table using a single statement:

INSERT INTO users (full_name)
           VALUES ('Jane Smith'), ('Harry Potter');

The syntax is very similar to when adding a single row, except each row of values is comma separated. As can be seen from the above example, you don't necessarily need to have each row on a separate line. It is generally good practice to do so though, as it enables you to clearly see the rows that you are adding and the values of those rows (in the case of our example this isn't too much of an issue since we are only adding two rows with one value each). Since we inserted two rows here, the count in the output response is 2.

One thing to note is that even though we are adding multiple rows at the same time, PostgreSQL adds them in the order that we specified in our statement. The nextval function therefore knows to set an id of 2 for 'Jane Smith' and id of 3 for 'Harry Potter'.

Here's what our table now looks like with all three rows of data added:

Table with Data Added

When inserting these three rows into our table, we've relied on a constraint, DEFAULT, for setting the last_login value for our first row, the enabled and last_login values for our second and third rows, and the id value for all three. Let's look at exactly what that means, and go over that and some other constraints you may encounter.

Constraints and Adding Data

We've covered constraints very briefly when setting up our table structure, but haven't yet really explained too much about them. Although constraints are set at the level of the table structure, or schema, and so are part of DDL, they are primarily concerned with controlling what data can be added to a table.

Default Values

Setting a DEFAULT value for a column ensures that if a value is not specified for that column in an INSERT statement, then the default value will be used instead. Three columns in our users table, id, enabled and last_login, have DEFAULT values set.

In our first INSERT statement we specified a value for enabled, but not for last_login, so our specified value was used for enabled and the default value used for last_login:

Adding one row with one default column

In our second INSERT statement we didn't specify a value for enabled or last_login, so the default values were used for both columns:

Adding two rows with two default columns

NOT NULL Constraints

It doesn't always make sense for a column to have a default value. For example, a column like full_name in the users table should contain a name that is specific to each user record, rather than some generic, default name. NOT NULL constraints can be used to ensure that when a new row is added, a value must be specified for that column.

If we try to execute the following INSERT statement:

INSERT INTO users (id, enabled)
           VALUES (1, false);

we receive the following response:

ERROR:  null value in column "full_name" violates not-null constraint
DETAIL:  Failing row contains (1, null, f, 2017-10-18 12:20:02.067639).

There are two things of interest here, the ERROR which tells us that we are in violation of the not-null constraint on the full_name column, and the DETAIL which shows the values in our failing row and specifically that the value we were trying to insert into the full_name column was null.

If our INSERT statement specifies both columns and values but we don't specify a particular column, SQL will try to insert null into that column by default. Since we have a NOT NULL constraint on our full_name column, that null gets rejected and we get an error.

Not Null constraint

Unique Constraints

Sometimes, rather than simply ensuring that a column has a value in it, we want to ensure that the value added for that column is unique; to do this we can use a UNIQUE constraint.

When we created our users table, we added a UNIQUE constraint to the id column. This type of constraint ensures that you can't have duplicate values in that column of the table. When we first created the table, we explained that an Index is created as a result of the UNIQUE constraint; on our users table, this Index is called users_id_key. Whenever we try to insert a row into our users table, the value that we specify for the id column is checked against existing values in the users_id_key Index; if the value already exists in there then we can't insert it into that column for our new row.

Unique constraint

We already have a row in our users table where the value in the id column is 1, so if we try to add another row with this same value for the id column, our UNIQUE constraint should prevent us from doing so.

INSERT INTO users (id, full_name, enabled)
           VALUES (1, 'Alissa Jackson', true);
ERROR:  duplicate key value violates unique constraint "unique_id"
DETAIL:  Key (id)=(1) already exists.

Just as intended, that unique constraint prevented duplicate data in our table. We can even check our current data within our table just to be sure:

SELECT * FROM users;
 id |  full_name   | enabled |         last_login
  1 | John Smith   | f       | 2017-10-25 10:26:10.015152
  2 | Jane Smith   | t       | 2017-10-25 10:26:50.295461
  3 | Harry Potter | t       | 2017-10-25 10:26:50.295461
(3 rows)

It's not unusual for a column such as id to have a UNIQUE constraint. Having some sort of 'id' column in a database table is a common, and useful, practice. Such a column is generally used to store a unique identifier for each row of data. In order for it to work effectively though, we need to ensure that each value in such a column is actually unique. Thus far, we've added data in such a way so that each id was unique and each record distinct, but we don't want to have to manually keep track of every value we add to that column; using a UNIQUE constraint lets PostgreSQL do the work for us.

Looking up values for UNIQUE constraints is just one use for indexes in a database. Database Indexes is a large and complex topic, worthy of a book on its own, and not one that we cover in any detail in this book, just remember that they come into play when a table column has a UNIQUE constraint.

CHECK Constraints

Certain columns in our table may not need unique values, but we may well want to ensure that the values entered into those columns conform to some other specific rules. In such a situation we can use a CHECK constraint. Check constraints limit the type of data that can be included in a column based on some condition we set in the constraint. Each time a new record is in the process of being added to a table, that constraint is first checked to ensure that data being added conforms to it.

Let's try this out by using a CHECK constraint on the full_name column. We want to ensure that every user record has a name. Right now, we ensure that null values can't be entered for a user's full name, but we don't guard against empty strings. For example, the values in following statement would be perfectly valid:

INSERT INTO users (id, full_name) VALUES (4, '');

Don't execute the above statement just yet, let's first fix this potential issue by adding a CHECK constraint to our users table:

ALTER TABLE users ADD CHECK (full_name <> '');

Now, if we were to try and add in a user with a blank name, we'll get an error back, similar to the one we received when we tried to add a record with a duplicate id.

INSERT INTO users (id, full_name) VALUES (4, '');
ERROR:  new row for relation "users" violates check constraint "users_full_name_check"
DETAIL:  Failing row contains (4, , t, 2017-10-25 10:32:21.521183).

Check constraint

A couple of things here that should be clarified. In case you haven't seen it before <> is an operator in SQL. It's a 'not equal' to operator (and alternative syntax for !=), and here we're using it to specify that any value we try to insert for full_name cannot be equal to an empty string.

Also, notice that we didn't specify a name for our constraint. We mentioned this short-hand syntax earlier, and now we're putting it to good use. If we don't need a specific name for our check constraint, then it's fine to leave the naming up to PostgreSQL.

A Bit About Quote Marks

A string in PostgreSQL is defined as a sequence of characters bounded by single quotes '. For example, 'This is a string'. What happens though if the string itself contains a single-quote character, such as in the name 'O'Leary'?

If we tried to use such a string in an INSERT statement, the statement would not execute properly since PostgreSQL would think that the second quote mark (after the O) was terminating a string, and the third one (after the y) was denoting the start of another string.

The way to deal with this situation is to use a second quote mark to escape the first, in the following manner 'O''Leary'.


In this chapter we've talked about one of the four types of DML interactions you can have with a database table, as well as adding, or 'creating', data using INSERT statements. We've covered a number of different aspects of adding data:

  • INSERT statement syntax
  • Table rows
  • Adding a single row of data
  • Adding multiple rows of data
  • Various types of constraint we can use to control what data is added:
    • DEFAULT values
    • NOT NULL constraints
    • UNIQUE constraints
    • CHECK constraints

Let's quickly recap some of the main commands:

Command Notes
INSERT INTO table_name (column1_name, column2_name, ...) VALUES (data_for_column1, data_for_column2, ...); creates a new record in table_name with the specified columns and their associated values.
ALTER TABLE table_name ADD UNIQUE (column_name); Adds a constraint to table_name that prevent non-unique values from being added to the table for column_name
ALTER TABLE table_name ADD CHECK (expression); Adds a constraint to table_name that prevents new rows from being added if they don't pass a *check* based on a specified expression.

That's all for now on adding data to a table and constraints. In the following chapters we'll work on expanding our knowledge of querying a database using SELECT.


  1. Make sure you are connected to the encyclopedia database. Add the following data to the countries table:

    Name Capital Population
    France Paris 67,158,000


    INSERT INTO countries (name, capital, population)
                 VALUES ('France', 'Paris', 67158000);
  2. Now add the following additional data to the countries table:

    Name Capital Population
    USA Washington D.C. 325,365,189
    Germany Berlin 82,349,400
    Japan Tokyo 126,672,000


    Here we could use separate INSERT statments for each row, or use a single statement to add multiple rows (as below).

    INSERT INTO countries (name, capital, population)
                 VALUES ('USA', 'Washington D.C.', 325365189),
                        ('Germany', 'Berlin', 82349400),
                        ('Japan', 'Tokyo', 126672000);
  3. Add an entry to the celebrities table for the singer and songwriter Bruce Springsteen, who was born on September 23rd 1949 and is still alive.

    PostgreSQL can accept date data in many different formats, including:


    More detailed information about the way PostrgeSQL deals with date and time inputs is outlined in the PostgreSQL documentation.


    INSERT INTO celebrities (first_name, last_name, occupation, date_of_birth, deceased)
                  VALUES ('Bruce', 'Springsteen', 'Singer, Songwriter', '1949-09-23', false);
  4. Add an entry for the actress Scarlett Johansson, who was born on November 22nd 1984. Use the default value for the deceased column.


    We can either omit the deceased column from our column list, in which case the default value for that column will automatically be used.

    INSERT INTO celebrities (first_name, last_name, occupation, date_of_birth)
                     VALUES ('Scarlett', 'Johansson', 'Actress', '1984-11-22');

    Alternatively we can include the deceased column, but use the DEFAULT keyword as the value for that column.

    INSERT INTO celebrities (first_name, last_name, occupation, date_of_birth, deceased)
                     VALUES ('Scarlett', 'Johansson', 'Actress', '1984-11-22', DEFAULT);
  5. Add the following two entries to the celebrities table with a single INSERT statement. For Frank Sinatra set true as the value for the deceased column. For Tom Cruise, don't set an explicit value for the deceased column, but use the default value.

    First Name Last Name Occupation D.O.B.
    Frank Sinatra Singer, Actor December 12, 1915
    Tom Cruise Actor July 03, 1962


    INSERT INTO celebrities (first_name, last_name, occupation, date_of_birth, deceased)
                    VALUES ('Frank', 'Sinatra', 'Singer, Actor', '1915-12-12', true),
                           ('Tom', 'Cruise', 'Actor', '1962-07-03', DEFAULT);
  6. Look at the schema of the celebrities table. What do you think will happen if we try to insert the following data?

    First Name Last Name Occupation D.O.B. Deceased
    Madonna Singer, Actress '08/16/1958' false
    Prince Singer, Songwriter, Musician, Actor '06/07/1958' true


    We can check the schema for the celebrities table using the \d meta-command in the psql console:

    \d celebrities
                                 Table "public.celebrities"
        Column     |          Type          |                         Modifiers
     id            | integer                | not null default nextval('famous_people_id_seq'::regclass)
     first_name    | character varying(80)  | not null
     occupation    | character varying(150) |
     date_of_birth | date                   | not null
     deceased      | boolean                | default false
     last_name     | character varying(100) | not null

    Since the last_name column has a NOT NULL constraint, and there are no values for this column in our input data, PostgreSQL will throw an error if we try to INSERT this data.

    ERROR:  null value in column "last_name" violates not-null constraint
  7. Update the last_name column of the celebrities table so that the data in the previous question can be entered, and then add the data to the table.


    First we need to alter the table column to drop the NOT NULL constraint:

    ALTER TABLE celebrities

    Then we can insert the data. We can do this by omitting the column from our column list (in which case the value of this column will automatically be NULL):

    INSERT INTO celebrities (first_name, occupation, date_of_birth, deceased)
                     VALUES ('Madonna', 'Singer, Actress', '1958-08-16', false),
                            ('Prince', 'Singer, Songwriter, Musician, Actor', '1958-06-07', true);

    Alternatively we can include the column in our column list but specify a NULL value for that column.

    INSERT INTO celebrities (first_name, last_name, occupation, date_of_birth, deceased)
                     VALUES ('Madonna', NULL, 'Singer, Actress', '1958-08-16', false),
                            ('Prince', NULL, 'Singer, Songwriter, Musician, Actor', '1958-06-07', true);
  8. Check the schema of the celebrities table. What would happen if we specify a NULL value for deceased column, such as with the data below?

    First Name Last Name Occupation D.O.B. Deceased
    Elvis Presley Singer, Musician, Actor '01/08/1935' NULL


    If we check the schema for the celebrities table using the \d meta-command, we can see that the deceased column has a DEFAULT constraint set:

    \d celebrities
                             Table "public.celebrities"
        Column     |          Type          |                         Modifiers
     id            | integer                | not null default nextval('famous_people_id_seq'::regclass)
     first_name    | character varying(80)  | not null
     occupation    | character varying(150) |
     date_of_birth | date                   | not null
     deceased      | boolean                | default false
     last_name     | character varying(100) |

    We know that if we omit the column from our column list then the default value will be used, but what if we actually specify the NULL as the value for a column with a DEFAULT constraint? Here, PostgreSQL actually sets a NULL value for that column rather than using the default.

    If you haven't already, execute the following SQL statement:

    INSERT INTO celebrities (first_name, last_name, occupation, date_of_birth, deceased)
                     VALUES ('Elvis', 'Presley', 'Singer, Musician, Actor', '1935-08-01', NULL);

    If you select all data from the celebrities table, you'll see that the deceased column for Elvis Presley is null.

    SELECT * FROM celebrities;
     id | first_name |             occupation             | date_of_birth | deceased |  last_name
      1 | Bruce      | Singer, Songwriter                 | 1949-09-23    | f        | Springsteen
      2 | Scarlett   | Actress                            | 1984-11-22    | f        | Johansson
      3 | Frank      | Singer, Actor                      | 1915-12-12    | t        | Sinatra
      4 | Tom        | Actor                              | 1962-07-03    | f        | Cruise
      7 | Madonna    | Singer, Actress                    | 1958-08-16    | f        |
      8 | Prince     | Singer, Songwriter, Musician, Actor | 1958-06-07    | t        |
      9 | Elvis      | Singer, Musician, Actor             | 1935-08-01    |          | Presley
    (7 rows)

    Generally you want to avoid boolean columns being able to have NULL values, since booleans, by their nature, should only have two states true and false. Throwing NULL into the mix creates three possible states. This is sometimes called the Three State Boolean problem or the Three Valued-logic problem.

  9. Check the schema of the animals table. What would happen if we tried to insert the following data to the table?

    Name Binomial Name Max Weight (kg) Max Age (years) Conservation Status
    Dove Columbidae Columbiformes 2 15 LC
    Golden Eagle Aquila Chrysaetos 6.35 24 LC
    Peregrine Falcon Falco Peregrinus 1.5 15 LC
    Pigeon Columbidae Columbiformes 2 15 LC
    Kakapo Strigops habroptila 4 60 CR

    Identify the problem and alter the table so that the data can be entered as shown, and then insert the data.


    The binomial_name column has a UNIQUE constraint on it, but Doves and Pigeons have the same binomial name.

    \d animals
                                           Table "public.animals"
           Column        |          Type          |                      Modifiers
     id                  | integer                | not null default nextval('animals_id_seq'::regclass)
     name                | character varying(100) | not null
     binomial_name       | character varying(100) | not null
     max_weight_kg       | numeric(10,4)          |
     max_age_years       | integer                |
     conservation_status | character(2)           |
        "unique_binomial_name" UNIQUE CONSTRAINT, btree (binomial_name)

    If we try adding this data to the table, then PostgreSQL will raise an error:

    ERROR:  duplicate key value violates unique constraint "unique_binomial_name"
    DETAIL:  Key (binomial_name)=(Columbidae Columbiformes) already exists.

    If we want separate entries for each in our table then we need to remove this contraint before adding the data.

    ALTER TABLE animals
      DROP CONSTRAINT unique_binomial_name;

    We can then add the data without any problems:

    INSERT INTO animals (name, binomial_name, max_weight_kg, max_age_years, conservation_status)
                 VALUES ('Dove', 'Columbidae Columbiformes', 2, 15, 'LC'),
                        ('Golden Eagle', 'Aquila Chrysaetos', 6.35, 24, 'LC'),
                        ('Peregrine Falcon', 'Falco Peregrinus', 1.5, 15, 'LC'),
                        ('Pigeon', 'Columbidae Columbiformes', 2, 15, 'LC'),
                        ('Kakapo', 'Strigops habroptila', 4, 60,'CR');
  10. Connect to the ls_burger database and examine the schema for the orders table.

    Based on the table schema and following information, write and execute an INSERT statement to add the appropriate data to the orders table.

    There are three customers -- James Bergman, Natasha O'Shea, Aaron Muller. James' email address is Natasha's email address is Aaron doesn't supply an email address.

    James orders a LS Chicken Burger, Fries and a Cola. Natasha has two orders -- an LS Cheeseburger with Fries but no drink, and an LS Double Deluxe Burger with Onion Rings and a Chocolate Shake. Aaron orders an LS Burger with no side or drink.

    The item costs and loyalty points are listed below:

    Item Cost ($) Loyalty Points
    LS Burger 3.00 10
    LS Cheeseburger 3.50 15
    LS Chicken Burger 4.50 20
    LS Double Deluxe Burger 6.00 30
    Fries 0.99 3
    Onion Rings 1.50 5
    Cola 1.50 5
    Lemonade 1.50 5
    Vanilla Shake 2.00 7
    Chocolate Shake 2.00 7
    Strawberry Shake 2.00 7


    In the customer_name column, for the rows with Natasha O'Shea we need to escape the single quote mark after the O by using a second single quote mark.

    Where an order doesn't include a particular item (burger, side, or drink) we have to specify a NULL value for the appropriate column. For the equivalent cost column, we could either explicitly use the DEFAULT or specify a value of 0.00 or 0.

    INSERT INTO orders (customer_name, customer_email, customer_loyalty_points, burger, side, drink, burger_cost, side_cost, drink_cost)
                VALUES ('James Bergman', '', 28, 'LS Chicken Burger', 'Fries', 'Cola', 4.50, 0.99, 1.50),
                       ('Natasha O''Shea', '', 18, 'LS Cheeseburger', 'Fries', NULL, 3.50, 0.99, DEFAULT),
                       ('Natasha O''Shea', '', 42, 'LS Double Deluxe Burger', 'Onion Rings', 'Chocolate Shake', 6.00, 1.50, 2.00),
                       ('Aaron Muller', NULL, 10, 'LS Burger', NULL, NULL, 3.00, DEFAULT, DEFAULT);