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.
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 :
INSERTstatements - These add new data into a database table
SELECTstatements - Also referred to as Queries; these retrieve existing data from database tables. We've worked with this type a bit already.
UPDATEstatements - These update existing data in a database table.
DELETEstatements - 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.
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
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 the
sql_book database via the psql console. Your command prompt should look like this:
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=#
So, 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
sql_book=# 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.
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:
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.
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.
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.
sql_book=# \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:
First let's try to add this row of data to
users without specifying the columns by running the
INSERT statement below.
sql_book=# 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
There's a couple of things we could do here.
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
last_login; for any columns that we omit, PostgreSQL will either use the default (if one has been set) or set the column to
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.
sql_book=# INSERT INTO users (full_name, enabled) sql_book-# 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 wont cover in this book. The second digit is the
count of rows that were inserted; since we inserted one row, the count is
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:
sql_book=# INSERT INTO users (full_name) sql_book-# 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. Although you don't need to have each row on a separate line as we have above, it is good practice to do so as it enables you to clearly see the rows that you are adding and the values of those rows. Since we inserted two rows here, the
count in the response is
One thing to note is that even though we are adding multiple rows, PostgreSQL adds them in the order that we specified in our statement. The
nextval function therefore knows to set an
2 for 'Jane Smith' and and
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
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.
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 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
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
In our second
INSERT statement we didn't specify a value for
last_login, so the default values were used for both columns:
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
sql_book=# INSERT INTO users (id, enabled) sql_book-# 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
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.
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
When we created our
users table, we added a
UNIQUE constraint to the on
idcolumn. 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
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.
sql_book=# INSERT INTO users (id, full_name, enabled) sql_book-# 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:
sql_book=# 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
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 users'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
sql_book=# ALTER TABLE users ADD CHECK (full_name <> ''); ALTER TABLE
Now, if we were to try and add in an 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.
sql_book=# 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.
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 is the string itself contains a single-quote character, such as in the name
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
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, 'creating', data using
INSERT statements. We've covered a number of different aspects of adding data:
Let's quickly recap some of the main commands:
|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
|ALTER TABLE table_name ADD CHECK (expression);||Adds a constraint to
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