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:
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
psql -d sql_book
This opens the psql console and connects to the database specified by the
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 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:
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;
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.
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;
create database my_database;
even though both options work. This is a pretty common convention, so we suggest you follow along with this guideline.
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
yet_another_database have been added to your list of databases.
When creating our
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
\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:
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
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.
\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
\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
\connect meta-commands to connect to the
Your command prompt should now look like this:
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
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.
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
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 -d sql_book
If you use the
\list meta-command now, you should see that
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
||displays all databases|
||connects to the sql_book database|
||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
.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
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|
||creates a new database called sql_book using a psql utility|
||permanently deletes the database named my_database and all its data|
Remember, that some of the utilities we've shown such as
dropdb are wrapper functions for actual SQL statements:
||creates a new database called sql_book|
||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
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
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
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
\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.
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
You are now connected to database "database_one" as user "user".
DROP DATABASE database_two;
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
\connect meta-commands. Once connected to a different database we can execute the
DROP DATABASE SQL statement to drop
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
First of all we need to quit the psql console and return to the Terminal. We can do this using the
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