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
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 Indexes: "users_pkey" PRIMARY KEY, btree (id)
Now we want to add an entry into the
users table with the following values:
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); INSERT 0 1
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.
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
library=# INSERT INTO users (username) VALUES ('John Smith'); INSERT 0 1
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.
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
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
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
library=# INSERT INTO users (username, enabled) VALUES ('Harry Potter', false); INSERT 0 1
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:
|\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|