PostgreSQL: Setup on the Local Machine

Setting up a local environment

We’re providing you with an execution environment with PostgreSQL running. If you want to later practice on your own machine, you will need a database server to play along with.

• If you’re using MacOSX, check out Postgres App to install a PostgreSQL server and the psql tool.

• For Windows, check this link.

• If you’re mainly running Linux, you know what you’re doing already, right? Look at the PostgreSQL packages for Debian and Ubuntu and install the most recent version of PostgreSQL on your station so that you have something to play with locally. For Red Hat packaging-based systems, check this out.

You might prefer a more visual tool such as pgAdminpgAdmin is an administration and development platform for PostgreSQL. or OmniDBOmniDB is a web tool that simplifies database management, focusing on interactivity, designed to be powerful and lightweight.; the key here is to be able to easily edit SQL queries, run them, edit them in order to fix them, see the explain plan for the query, etc.

The psqlrc setup

Here we begin with a full setup of psql—we’re going to get back to each important point separately. Doing so allows us to have a fully working environment from the get-go and play around in our PostgreSQL console.

\set PROMPT1 '%~%x%# '
\x auto
\set ON_ERROR_STOP on
\set ON_ERROR_ROLLBACK interactive

\pset null '¤'
\pset linestyle 'unicode'
\pset unicode_border_linestyle single
\pset unicode_column_linestyle single
\pset unicode_header_linestyle double
set intervalstyle to 'postgres_verbose';

\setenv LESS '-iMFXSx4R'
\setenv EDITOR '/Applications/Emacs.app/Contents/MacOS/bin/emacsclient -nw'

Save that setup in the ~/.psqlrc file, which is read at startup by the psql application.

As we’ve already read in the PostgreSQL documentation for psql, we have three different settings to play with here:

• \set [ name [ value [ ... ] ] ]: This sets the psql variable name to value, or if more than one value is given, to the concatenation of all of them. If only one argument is given, the variable is set with an empty value. To unset a variable, use the \unset command.

• \setenv name [ value ]: This sets the environment variable name to value, or if the value is not supplied, it unsets the environment variable.

  Here we use this facility to set up specific environment variables we need from within psql, such as the LESS setup. It allows invoking the pager for each result set but having it take control of the screen only when necessary.

• \pset [ option [ value ] ]: This command sets options affecting the output of query result tables. The variable option indicates which option is to be set. The semantics of value vary depending on the selected option. For some options, omitting value causes the option to be toggled or unset, as described under the particular option. If no such behavior is mentioned, then omitting value just results in the current setting being displayed.

Transactions and psql behavior

In our case, we set several psql variables that change their behavior:

• \set ON_ERROR_STOP on: The name is quite a good description of the option. It allows psql to know that it is not to continue trying to execute all our commands when a previous one is throwing an error. It’s primarily practical for scripts and can also be set using the command line. As we’ll see later, we can easily invoke scripts interactively within our session with the \i and \ir commands, so the option is still useful to us now.

• \set ON_ERROR_ROLLBACK interactive

  This setting changes how psql behaves with respect to transactions. It’s a very good interactive setup and must be avoided in batch scripts.

With the \set PROMPT1 '%~%x%# ' that we are using, psql displays a little star in the prompt when there’s a transaction in flight, so we know we need to finish the transaction. More importantly, when we want to type in anything that will have a side effect on our database (modifying the data set or the database schema), then without the star, we know we need to first type in BEGIN.

Let’s see an example output. Here’s with the default value; we can run these commands one by one in the terminal in the Try it yourself section:

f1db# begin;
BEGIN
f1db*# select 1/0;
ERROR:  division by zero
f1db!# select 1+1;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
f1db!# rollback;
ROLLBACK

We have an error in our transaction, and we notice that the star prompt is now a flag. The SQL transaction is marked invalid, and the only thing PostgreSQL will now accept from us is to finish the transaction with either a commit or a rollback command. Both will result in the same result from the server: ROLLBACK.

Let’s do the same SQL transaction again, this time with ON_ERROR_ROLLBACK being set to interactive. From the documentation:

“When set to on, if a statement in a transaction block generates an error, the error is ignored, and the transaction continues. When set to interactive, such errors are only ignored in interactive sessions and not when reading script files. When unset or set to off, a statement in a transaction block that generates an error aborts the entire transaction.”