Create and View Tables

Create a Table

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

$ psql -d library
psql (9.5.1)
Type "help" for help.

library=#

There is also an alternate syntax for connecting to a database with psql. 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.

  1. CREATE TABLE: Firstly the CREATE TABLE users is the primary command.
  2. users: The name of the table that will be created.
  3. (): The information in the parentheses is related to the columns in the table.
  4. id, username, enabled: These are the three columns of the table.
  5. PRIMARY KEY: The id is the primary key of the table. We'll talk about what that PRIMARY KEY is 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
id serial
username CHAR(25) NOT NULL
enabled boolean DEFAULT TRUE

What do Data Type and Properties mean?

  • 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(25). 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.

What is a Primary Key?

The 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 users table.

View the Table

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

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?

The \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)

Summary

Let's quickly recap again:

Command Notes
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.