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.

Setup

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:

sql_book=#

Now execute the following SQL statement:

CREATE TABLE users (
    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:

CREATE TABLE
sql_book=#

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

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, ...);

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

Rows

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:

INSERT 0 1

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');

INSERT 0 2

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:

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:

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:

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.

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.

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.

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).

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.

Summary

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.