Your First Database

In this chapter, you will create your first database. Follow along the instructions step by step, to work through the commands provided. Each set of commands builds on the foundation of the previous set, thereby demonstrating the SQL commands as they apply to the concepts of designing, creating and manipulating data.

Pro Tip

If you start to get errors in any step, then run the command dropdb library and start over.

Also, keep in mind that some of these commands are run from within psql console, while other are run from the main terminal prompt. Commands like dropdb and createdb are run from outside psql. Commands like \list and \dt are run from within psql.

  • Commands from within the terminal prompt will start with a $.
  • Commands from within the psql console should start with the database name followed by =#; something like: sql_book=#.

Create a database

If you're currently at the command prompt, which should look like sql_book=#, you can view the current databases available with the \l or \list command, as shown below:

sql_book=#  \list
                                List of databases
Name           |   Owner   | Encoding |   Collate   |    Ctype    |    Access privileges
---------------+-----------+----------+-------------+-------------+-------------------------
postgres       |   User    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
sql_book       |   User    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template0      |   User    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/User            +
               |           |          |             |             | User=CTc/User
template1      |   User    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | User=CTc/User      +
               |           |          |             |             | =c/User
(4 rows)

Note, that "User" will be the name of the currently logged in User on your computer. "User" is used here for the purposes of this example.

Despite the fact that we have only created one database so far, the PostgreSQL output above is listing some databases that come with it built-in.

Now, let's practice creating a new database. We'll name it library and use the createdb command to make it:

$ createdb library
sql_book=# \list
                                List of databases
Name           |   Owner   | Encoding |   Collate   |    Ctype    |    Access privileges
---------------+-----------+----------+-------------+-------------+-------------------------
postgres       |   User    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
library        |   User    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
sql_book       |   User    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template0      |   User    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/User            +
               |           |          |             |             | User=CTc/User
template1      |   User    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | User=CTc/User      +
               |           |          |             |             | =c/User
(5 rows)

Database naming

There are some conventions you should be aware of when naming a database. As a guide, always try to keep database names self-descriptive. A descriptive name is especially helpful if you end up having a lot of databases. A database containing information about Employees could be named 'employees' or 'employee_database'. A less descriptive name might be 'emp' or 'records'. Also, as you can see, database names should be written in snake_case, that is, lowercase with words separated by underscores.

Convention: Uppercase Commands, Lowercase names

Throughout the book, we use the convention of uppercase for SQL commands and lowercase names for tables and databases. This convention is for clarity only and is used by many SQL developers, but SQL itself is not case-sensitive.

Select the Database

When we are in the psql console, we can change databases by using the \c dbname command. Let's give it a try. From within psql lets connect to the library database we just made:

sql_book=# \c library
You are now connected to database "library" as user "User".
library=#

We've now created and selected a database for use, but we have no tables! We can check the current list of tables by using the \dt command.

library=# \dt
No relations found.

Since we haven't created any tables in this database, this is precisely what we'd expect.

Delete the Database

Occasionally you're going to want to delete a database. PostgreSQL has a special command, dropdb to accomplish this purpose. This was mentioned earlier, but let's get a bit more practice with it. We can delete our recently created library database by using the dropdb command

$ dropdb library

Caution This command should be used with care as it is permanent and cannot be reversed. All data and schema related to the database is deleted.

Summary

We're just dipping our toes in, but we already have a small handful of commands, so let's quickly recap. First we have some commands that can be used within a psql session:

PSQL Command Notes
\l or \list displays all databases
\c library change current database to library
\dt displays all tables for the current database

We also have some commands that are programs installed by PostgreSQL on our system:

Command-line Command Notes
psql -d library Start a psql session and connect to the library database
createdb library creates a new database called library
dropdb library permanently deletes the database named library and all its data

In the next chapter, we will create a table and add to the database.