In the previous chapter we talked about how schema and data work together to provide us with structured data with which we can interact in various, useful ways. In this section we're going to focus on schema, and how it provides the structure to house our data.
Thus far we've only very briefly discussed schema, and that's been mostly in terms of tables and table columns. We'll look at tables more closely in the next couple of chapters; before we can work with tables, however, we need somewhere for the tables to exist: we need to create the database itself.
A schema is also part of the database. We can perhaps think of a building as a database. The floor plan would then be the schema, while the physical rooms correspond to the tables in the database. The rooms are perhaps different sizes, have different things in them, and some of them might be connected to each other, but they all exist within the schema (the floor plan) of the building (database).
In this chapter, you will create your first database. Recall that there are three SQL sub-languages: Data Definition Language, Data Manipulation Language, and Data Control Language. We will be using Data Definition Language, or DDL, to create our database, since this deals with setting up the structure, or schema, of a database.
As the name implies, Data Definition Language is focused on defining the characteristics of the database and its tables and columns. This includes creating databases and tables, or altering and changing finer details about your database, such as table names and column data types. But it does not deal with the data within a database.
Follow along the below instructions step by step to work through the meta-commands and SQL statements provided.
If you're currently in the psql console (e.g. from working through the tutorial in the previous chapter), then type in \q and hit 'enter' to return to the terminal.
Let's create a new database. We'll name it sql_book and use the createdb command to create it:
createdb sql_book
Now that our new sql_book database has been created, we should be able to connect to it via the psql console. We'll use the psql command, passing it the -d option with the database name sql_book:
psql -d sql_book
This opens the psql console and connects to the database specified by the -d option.
If you see the prompt below (ignoring the version number, which may be different), then everything is working as intended:
psql (9.5.3)
Type "help" for help.
sql_book=#
Notice that the prompt in the psql console is the database that we are connected to.
We can also see that a new database has been added to our current list of databases by using the \list meta-command. Let's try that now.
\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)
After you install PostgreSQL, you'll start out with four databases: template0, template1, postgres, and a database for the currently logged in user. And the output above shows, whenever you create a new database it will be added to the running list of databases.
We created our sql_book database from the terminal using the PostgreSQL client application createdb. In an earlier chapter we explained that some PostgreSQL client applications essentially act as 'wrappers' around actual SQL syntax; createdb is one of these. Using createdb can offer us a nice shortcut, but the same operation can be accomplished using the SQL statement CREATE DATABASE. Remember that utility functions are executed from the terminal, while SQL statements are executed from within the psql console.
For example, if we wanted to create another database from the psql console, we could do so with a SQL statement like this:
CREATE DATABASE another_database;
CREATE DATABASE
On the first line we are using the CREATE DATABASE SQL command with another_database as the value for 'name' for that command. Note that we terminate our statement with a semi-colon ; in order to tell PostgreSQL that the command ends here and that it should now execute the command.
The CREATE DATABASE in the PostgreSQL's output lets us know that it has executed the statement successfully.
Convention: Uppercase Commands, Lowercase names
Throughout the book, we use the convention of uppercase for SQL statements 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. For example, we will write:
CREATE DATABASE my_database;
instead of:
create database my_database;
even though both options work. This is a pretty common convention, so we suggest you follow along with this guideline.
Use the CREATE DATABASE command to create one more database called yet_another_database in the same way. Once you have done this, use the list meta-command to check that both another_database and yet_another_database have been added to your list of databases.
When creating our another_database and yet_another_database databases, we only passed a single parameter, 'name' to the CREATE DATABASE command. This parameter is mandatory. It is worth noting that there are other, optional, parameters which set things such as the database's encoding, collation, connection limit, and so on. These other parameters are beyond the scope of this book; when omitted, PostgreSQL simply uses the default settings for these parameters.
There are some conventions you should be aware of when naming a database. As a guideline, 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, database names should be written in snake_case, that is, lowercase with words separated by underscores.
In order to be able to issue commands to a database we need to be connected to it. At the start of this chapter, we saw that from terminal we can open the psql console and connect to a database using the psql command with the -d option and a database name.
When we are in the psql console itself, we can connect to a different database by using the \c or \connect meta-commands (these both do the same thing). Let's try this out.
We should currently be connected to the sql_book database that we connected to earlier. Let's now use the \c meta-command to connect to the another_database database you created:
\c another_database
You are now connected to database "another_database" as user "User".
another_database-#
On the first line we were connected to the sql_book database, as can be seen by the prompt. We used the \c meta command, passing it another_database as the 'dbname' argument.
The output simply informs us that we have successfully connected to the database another_database. When we open a connection to another_database in this way, the previous connection is closed. We are now connected to another_database and not sql_book.
Notice that the prompt has now changed to another_database to show that we are now connected to it. Also notice that meta-commands, unlike SQL statements, do not have to be terminated with a semi-colon.
The \c and \connect meta-commands can take arguments other than 'dbname', such as 'username', 'host', 'port', etc. When omitted, the command reuses the values from the previous connection. If connecting to a locally installed database you can generally omit these other arguments entirely.
The pieces of connection information represented by these arguments aren't specific to the \c and \connect meta-commands, or even to PostgreSQL. This type of connection information is generally required whichever interface you are using to connect to a database, particularly when connecting to databases that are hosted remotely, or on a different server to the application or system that is connecting to that database.
Let's now disconnect from another_database and connect to yet_another_database instead. Use the \c or \connect meta-commands to connect to the yet_another_database database.
Your command prompt should now look like this:
yet_another_database=#
Occasionally you're going to want to delete a database. We can use the SQL command DROP DATABASE to do this. The syntax is pretty much the same as that for CREATE DATABASE; the command, followed by the name of the database we want to delete, followed by a semi-colon. Let's use the DROP DATABASE to delete the
another_database database:
DROP DATABASE another_database;
DROP DATABASE
yet_another_database=#
The first line above is our SQL statement. The DROP DATABASE in the output is the response returned by PostgreSQL to let us know that it has executed the statement successfully. Our prompt is again available on the third line, ready for us to issue another command.
In the same way that createdb is a PostgreSQL client application that acts as a wrapper for the CREATE DATABASE SQL command, dropdb performs the same role for the DROP DATABASE SQL command.
To use dropdb, we need to issue the command from terminal rather than the psql console. Use \q to quit the psql console and return to terminal. From terminal, let's use dropdb to delete the yet_another_database database.
dropdb yet_another_database
Caution
The DROP DATABASE and dropdb commands should be used with extreme care, as their actions are permanent and cannot be reversed. When these commands are issued, all data and schema related to the database is deleted. It's best to take a minute and think carefully before issuing these commands.
Re-open the psql console and connect to the sql_book database by using the psql command:
psql -d sql_book
If you use the \list meta-command now, you should see that another_database and yet_another_database are no longer in the list of databases.
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 Meta-Command | Notes |
|---|---|
\l or \list
|
displays all databases |
\c sql_book or \connect sql_book
|
connects to the sql_book database |
\q |
exits the PostgreSQL session and return to the command-line prompt |
One meta-command that we haven't met yet, but that you may find useful is the \e command, which calls up an editor that lets you edit and rerun the previous command. By default PostgreSQL uses the vim editor to edit and save queries for execution when you use the \e metacommand. You may want to change the default editor used by \e to one you're more accustomed to; add the following lines of code to either your .bashrc or .zshrc file, depending on whether you're using a bash shell or a zsh shell.
$ export EDITOR="cli_editor_command -w"
$ export VISUAL="cli_editor_command -w"
cli_editor_command is a placeholder for the command you use to launch your editor of choice from the command line. For instance, the command to open Visual Studio Code is code.
Once you make these changes, you will need to restart your Terminal session.
Note that some command line shortcuts for code editors aren't useable immediately after installing your editor of choice; you may have to add the associated command's directory to your $PATH first and/or symlink the command necessary to launch your editor from the terminal.
We also have some commands that are programs installed by PostgreSQL on our system:
| Client/Application Command-Line Command | Notes |
|---|---|
psql -d sql_book |
starts a psql session and connect to the sql_book database |
createdb sql_book |
creates a new database called sql_book using a psql utility |
dropdb my_database |
permanently deletes the database named my_database and all its data |
Remember, that some of the utilities we've shown such as createdb and dropdb are wrapper functions for actual SQL statements:
| SQL Statement | Notes |
|---|---|
CREATE DATABASE sql_book |
creates a new database called sql_book |
DROP DATABASE my_database |
permanently deletes the database named my_database and all its data |
In the next chapter, we will create a table and go into more specific details about DDL and altering our database structure, or database schema.
From the Terminal, create a database called database_one.
createdb database_one
To create a database from the Terminal, we can use the PostgreSQL client application createdb, and pass it the name of the database that we want to create.
From the Terminal, connect via the psql console to the database that you created in the previous question.
psql -d database_one
Here we can use the PostgreSQL client application psql, passing it the -d option followed by the name of the database to which we want to connect. This opens a psql console session in our terminal window and connects to the database we specified.
psql (9.5.3)
Type "help" for help.
database_one=#
From the psql console, create a database called database_two.
CREATE DATABASE database_two;
CREATE DATABASE
database_one=#
From the psql console, we can execute SQL statements. Here we use the CREATE DATABASE command along with the database name and terminate our statement with a ;. In response we get CREATE DATABASE, so we know that our statement has been executed successfully.
From the psql console, connect to database_two.
\c database_two
or
\connect database_two
With either command, the output is:
You are now connected to database "database_two" as user "user".
database_two=#
To connect to a different database from within the psql console we can use the \c or \connect meta-commands. This disconnects us from the current database, connects us to the new one, and changes the psql console prompt accordingly.
Display all of the databases that currently exist.
\list
We can use the \list meta-command to return a list of databases. The output should look something like this:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------------+-------------+----------+-------------+-------------+-------------------------
database_one | User | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
database_two | User | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
ls_burger | User | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
sql_book | User | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/User +
| | | | | User=CTc/User
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | User=CTc/User +
| | | | | =c/User
(7 rows)
From the psql console, delete database_two.
\connect database_one
You are now connected to database "database_one" as user "user".
DROP DATABASE database_two;
DROP DATABASE
Since we're currently connected to database_two, we first need to disconnect from it and connect to another database. We can do that using the \c or \connect meta-commands. Once connected to a different database we can execute the DROP DATABASE SQL statement to drop database_two.
If we had tried executing this same statement while connected to database_two, we would have received an error:
DROP DATABASE database_two;
ERROR: cannot drop the currently open database
From the Terminal, delete the database_one and ls_burger databases.
First of all we need to quit the psql console and return to the Terminal. We can do this using the \q meta-command:
\q
Once in the Terminal we can use the PostgreSQL client application dropdb, and pass it the name of the database that we want to drop.
$ dropdb database_one
$ dropdb ls_burger
$
We don't receive any kind of specific response to say that our request has been completed, however, if you now try to connect to either of the databases that you dropped you should receive a response saying that the database does not exist:
$ psql -d database_one
psql: FATAL: database "database_one" does not exist