Insert data in Table

The Insert Command

If you don't already have a database named "library", then make sure to run createdb library from the terminal before starting this chapter. The library database is needed to work through this chapter's content. The library database can be restored from a backup file. First download the backup file for this chapter from here.

Then run the following command: psql -d library < add_data.sql

Now that we have created the library database, and added a users table to that database, we need to add some data to the table. We can do that with the INSERT command.

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.

library=# \d users
                             Table "public.users"
  Column  |     Type      |                     Modifiers
 id       | integer       | not null default nextval('users_id_seq'::regclass)
 username | character(25) | not null
 enabled  | boolean       | default true
    "users_pkey" PRIMARY KEY, btree (id)

Now we want to add an entry into the users table with the following values:

  • id: 20
  • username: 'John Smith'
  • enabled: false

We can add this row of data to users by running the INSERT statement below. This statement adds data based on column names and values passed for those specific columns.

library=# INSERT INTO users (id, username, enabled)
          VALUES (20,'John Smith', false);

Note that the order of the columns must match the order of the values to be inserted.

Let's add a new row with exactly the same values. What happens when we run it? We get a SQL error. When we created the users table, we set the id column as the primary key. Therefore, PostgreSQL will prevent duplicate data from being added to this column. All values in the primary key column must be unique throughout that table.

library=# INSERT INTO users (id, username, enabled)
          VALUES (20,'John Smith', false);
ERROR:  duplicate key value violates unique constraint "users_pkey"
DETAIL:  Key (id)=(20) already exists.

DEFAULT property

If you scroll up and look at the details of the users table for the enabled column, the DEFAULT property of the enabled column is set to true. PostgreSQL will use the default value listed if no value is set when a new record is inserted. If we insert a new record into users, and we don't specify the value of the enabled column, then it will automatically be set to true.

library=# INSERT INTO users (username)
         VALUES ('John Smith');

If we looked at the record we just inserted into the users table, we would see that enabled would be set to t, a value of true.

nextval function

If you scroll up a bit and look at the users table again, you'll notice that the id column utilizes a function call . When we created the table in the last chapter, we attached this function nextval to the id column.

The nextval function is used in conjunction with the default property. Each time a new record is inserted into the database, nextval increments the last id by one and sets that value as the default value of the current record's id. Because the id column has the nextval property, we should not include it when inserting values and let PostgreSQL automatically generate a value for the id column.

Notice that in the insertion code below we did not specify a value for the id column or even list the id column at all. A unique number was automatically generated by PostgreSQL for the id value. The query below, is the correct way to insert data in a column that has the nextval function.

library=# INSERT INTO users (username, enabled)
          VALUES ('Harry Potter', false);

NOT NULL property

We cannot, however, skip the username column, since it does not have any default value, and also it does not allow an empty or NULL value. Look at the users table again, the Null property for the username column is set to NOT, which means that this column must contain a value. What happens when you try to call the INSERT command without this value? Run the query below.

library=# INSERT INTO users (enabled)
          VALUES (false);
ERROR:  null value in column "username" violates not-null constraint
DETAIL:  Failing row contains (2, null, f).

We have inserted several rows of data into the users table, but have no idea what the data looks like and what is happening to it. In order to view the data inside the table, we will need to run SELECT queries. In the next chapter, we will look at how the SELECT works. Play close attention as this is the most used command and also the command with the most clauses.


Let's quickly recap again:

Command Notes
\d users displays the structure of a table
INSERT INTO users (username) VALUES ('John Smith') creates a new record in users table with username John Smith