The psql Console

To check installation, we will connect to the Postgres server using the psql console that we installed and started.

Throughout this book, we will be using the command-line client to connect to the Postgres server that we started after installation. What this means is that you'll be typing commands into a prompt that looks like this:

$

Once you are connected to a database, you'll be using the psql console. Its prompt looks something like this:

sql_book=#

PostgreSQL is more tightly coupled to the UNIX environment than some other database systems. It uses the native user accounts to determine who is connecting to it (by default). Many of the commands that you may need to run are available as executable programs that can be run from anywhere on the machine the database is running on, assuming that the user running them has the correct permissions.

Creating a Database

$ createdb sql_book

createdb is the command used to create a new PostgreSQL database. The command is followed by the name of the database we wish to create, in this case that is sql_book.

Connecting to a Database

There are several ways to connect to a database server. We will mostly use the text-based psql console program in this book. To connect to a database, you'll need to

$ psql -d sql_book

Some Useful psql Commands

Command Description Example
\c $dbname Connect to database $dbname. \c blog_development
\d Describe available relations
\d $name Describe relation $name \d users
\? List of console commands and options
\h List of available SQL syntax Help topics
\h $topic SQL syntax Help on syntax for $topic \h INSERT
\q Quit

Since console commands are not SQL statements, they do not need to be terminated with a semicolon.

After you issue a Postgres command, you'll receive feedback from the server telling you the result of a command, or showing you the results of a request for information. For instance, if you want to know what version of PostgreSQL you're currently using, you can do the following (information on functions and how to use them such as VERSION() will be included in later chapters):

sql_book=# SELECT VERSION();
                                             version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 9.4.5 on x86_64-apple-darwin15.0.0, compiled by Apple LLVM version 7.0.0 (clang-700.1.76), 64-bit
(1 row)

As you can see, the command issued to the server returns the version number of the PostgreSQL server.