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 to 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=#
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
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, ...);
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:
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.
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.
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.
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
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
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
2 for 'Jane Smith' 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
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
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
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.
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 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
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.
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
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, or '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
Make sure you are connected to the
encyclopedia database. Add the following data to the
INSERT INTO countries (name, capital, population) VALUES ('France', 'Paris', 67158000);
Now add the following additional data to the
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);
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:
1999-01-08 1999-Jan-08 Jan-08-1999 08-Jan-1999 99-Jan-08 08-Jan-99 Jan-08-99 19990108
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);
Add an entry for the actress Scarlett Johansson, who was born on November 22nd 1984. Use the default value for the
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);
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);
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|
|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
\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
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
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 ALTER COLUMN last_name DROP NOT NULL;
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
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);
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
NULL into the mix creates three possible states. This is sometimes called the Three State Boolean problem or the Three Valued-logic problem.
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|
|Golden Eagle||Aquila Chrysaetos||6.35||24||LC|
|Peregrine Falcon||Falco Peregrinus||1.5||15||LC|
Identify the problem and alter the table so that the data can be entered as shown, and then insert the data.
binomial_name column has a
UNIQUE constraint on it, but Doves and Pigeons have the same binomial name.
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) | Indexes: "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');
Connect to the
ls_burger database and examine the schema for the
Based on the table schema and following information, write and execute an
INSERT statement to add the appropriate data to the
There are three customers -- James Bergman, Natasha O'Shea, Aaron Muller. James' email address is email@example.com. Natasha's email address is firstname.lastname@example.org. 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 Chicken Burger||4.50||20|
|LS Double Deluxe Burger||6.00||30|
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
INSERT INTO orders (customer_name, customer_email, customer_loyalty_points, burger, side, drink, burger_cost, side_cost, drink_cost) VALUES ('James Bergman', 'email@example.com', 28, 'LS Chicken Burger', 'Fries', 'Cola', 4.50, 0.99, 1.50), ('Natasha O''Shea', 'firstname.lastname@example.org', 18, 'LS Cheeseburger', 'Fries', NULL, 3.50, 0.99, DEFAULT), ('Natasha O''Shea', 'email@example.com', 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);