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.
In this chapter, we will create a table for the
library database. Since there may be several databases, we need to make sure we are using the correct one. So, let's access the
library database with the
$ psql -d library psql (9.5.1) Type "help" for help. library=#
There is also an alternate syntax for connecting to a database with
We can use
psql -d db_name. Or if the
db_name is the last argument to the
psql command, we can use this syntax:
psql db_name. They both perform the same function; whichever syntax you use is up to you.
In the library database we want to store a list of users, for which we will create our first table. We will store a user's username and whether they have access to the library.
Below is the SQL statement to create a table, named users, using the
CREATE TABLE statement:
library=# CREATE TABLE users ( id serial, username CHAR(25) NOT NULL, enabled boolean DEFAULT TRUE, PRIMARY KEY (id) );
That's quite a large and daunting command to enter, so let's break it down a little.
CREATE TABLE: Firstly the
CREATE TABLE usersis the primary command.
users: The name of the table that will be created.
id,username, enabled: These are the three columns of the table.
PRIMARY KEY: The id is the primary key of the table. We'll talk about what that
PRIMARY KEYis later in this chapter.
The three columns,
id,username, enabled were created with different data types and properties that are described in the table below. A data type classifies particular values that are allowed for that column.
|Column Name||Column Data Type||Extra Properties/Constraints|
serial: serial is used to create identifier columns for a PostgreSQL database. It is similar to using
AUTO_INCREMENT found in other relational database management systems. Specifying
serial is equivalent to running the following command:
CREATE SEQUENCE tablename_colname_seq;
And then including the following datatypes and properties inside the command to create our table:
colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
This setting ensures that this column will be of type integer, non-null, and that each id will be sequential if the id isn't directly specified.
serial does not prevent the insertion of duplicate ids.
CHAR(25): In case of the column
username, the data type is
CHAR is short for character. Therefore, the username field can hold values of up to 25 characters.
boolean: In case of the column
enabled, the data type is
boolean. A boolean datatype can have only two values TRUE/FALSE.
NOT NULL: The column
username has an extra property
NOT NULL, which essentially means username MUST have a value, it cannot be left empty.
DEFAULT: The column
enabled has an extra property
DEFAULT with a value of
TRUE. If no value is set in this field when a record is created then the value of
TRUE is set in that field. We will see more on
DEFAULT property when we
INSERT data into this table.
Semicolons in PostgreSQL
If you type that huge command into the psql console, you should see output that looks something like CREATE TABLE. You may have noticed that the command above was typed on multiple lines. It is possible to type on multiple lines because until PostgreSQL receives the semicolon character, it will take all input as a single command.
PRIMARY KEY (id) option specifies the
id column as a primary key of this table.
In order for the database to keep track of all the records, each record (or row of data) must be uniquely identifiable in some way. For this purpose, databases use a concept called primary keys.
A primary key is a column that acts as a unique identifier for a row. A table can only have one primary key column, and all values in the primary key column must be unique throughout the table. In the example above, we're specifying the
id field as the primary key for the
How do we check if the table got created in our database?
The meta command
\dt shows you the list of tables in your database. Since we have only created one, there is only one row with our table name
library=# \dt List of relations Schema | Name | Type | Owner --------+-------+-------+----------- public | users | table | User (1 row)
We can see the
users table, but what are its columns, and what are the column data types and properties?
\d meta command shows you that information. This lets us describe a table. So we use
\d users to see information on the users table. In the output below, each row is a column in the
users table, along with that column's properties.
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)
Let's quickly recap again:
|psql -d library||Select the database to use|
|CREATE TABLE users..||Creates a new table called users|
|\dt||Shows the tables in the current database|
|\d users||Shows the schema of the table|
In the next chapter, we will look at adding data to our database.