Book Image

PostgreSQL 9 Administration Cookbook - Second Edition

Book Image

PostgreSQL 9 Administration Cookbook - Second Edition

Overview of this book

Table of Contents (19 chapters)
PostgreSQL 9 Administration Cookbook Second Edition
Credits
About the Authors
About the Reviewers
www.PacktPub.com
Preface
Index

Using the psql query and scripting tool


psql is the query tool supplied as a part of the core distribution of PostgreSQL, so it is available and works similarly in all environments. This makes it an ideal choice for developing portable applications and techniques.

psql provides features for use as both an interactive query tool and a scripting tool.

Getting ready

From here on, we will assume that psql is a sufficient command to allow you access to the PostgreSQL server. This assumes that all of your connection parameters are defaults, which may not be true.

Written in full, the connection parameters would be as follows:

psql –h hostname –p 5432 –d dbname –U username

The default value for the port (-p) is 5432. By default, dbname and username are both identical to the operating system's username. The default hostname on Windows is localhost, while on Unix, we use the default directory for Unix socket connections. The location of such directories varies across distributions and is set at compile time. However, note that you don't actually need to know its value because on local connections, both the server and the client are normally compiled together, so they use the same default.

How to do it…

The command that executes a single SQL command and prints the output is the easiest, as shown here:

$ psql -c "SELECT current_time"
     timetz
-----------------
 18:48:32.484+01
(1 row)

The –c command is noninteractive. If we want to execute multiple commands, we can write those commands in a text file and then execute them using the –f option. This command loads a very small and simple set of examples:

$ psql –f examples.sql

It produces the following output when successful:

SET
SET
SET
SET
SET
SET
DROP SCHEMA
CREATE SCHEMA
SET
SET
SET
CREATE TABLE
CREATE TABLE
COPY 5
COPY 3

The examples.sql script is very similar to a dump file produced by PostgreSQL backup tools, so this type of file and the output it produces are very common. When a command is executed successfully, PostgreSQL outputs a "command tag" equal to the name of that command; this is how the preceding output was produced.

psql can also be used in interactive mode, which is the default, so it requires no option:

$ psql
postgres=#

The first interactive command you'll need is the following:

postgres=# help

You can then type in SQL or other commands. The last interactive command you'll need is this:

postgres=# \quit

Unfortunately, you cannot type quit on its own, nor can you type \exit or other options. Sorry, just \quit or \q for short!

How it works…

In psql, you can enter the following two types of commands:

  • psql meta-commands

  • SQL

A meta-command is a command for the psql client, whereas SQL is sent to the database server. An example of a meta-command is \q, which tells the client to disconnect. All lines that begin with \ (backslash) as the first nonblank character are presumed to be meta-commands of some kind.

If it isn't a meta-command, then it's SQL. We keep reading SQL until we find a semicolon, so we can spread SQL across many lines and format it any way we find convenient.

The help command is the only exception. We provide this for people who are completely lost, which is a good thought; so let's start from there ourselves.

There are two types of help commands, which are as follows:

  • \?: This provides help on psql meta-commands

  • \h: This provides help on specific SQL commands

Consider the following snippet as an example:

postgres=# \h DELETE
Command:     DELETE
Description: delete rows of a table
Syntax:
DELETE FROM [ ONLY ] table [ [ AS ] alias ]
    [ USING usinglist ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ AS output_name ] [,]]

I find this a great way to discover or remember options and syntax. You'll also like the ability to scroll back through the previous command history.

You'll get a lot of benefit from tab completion, which will fill in the next part of the syntax, just by pressing the Tab key. This also works for object names, so you can type in just the first few letters and then press Tab; all the options will be displayed. Thus, you can type in just enough letters to make the object name unique and then hit Tab to get the rest of the name.

One-line comments are used with two dashes, as follows:

-- This is a single-line comment

Multiline comments here are similar to those in C and Java:

/*
 * Multi-line comment
 */

You'll probably agree that psql looks a little daunting at first, with strange backslash commands. I do hope you'll take a few moments to understand the interface and keep digging for more information. psql is one of the most surprising parts of PostgreSQL, and it is incredibly useful for database administration tasks when used alongside other tools.

There's more…

psql works across releases, though in older versions you may see a message like the following, if you do so:

psql on Windows can be a little problematic, but things are constantly improving. I recommend using a terminal emulator to connect to your server and accessing psql from there.

See also

Check out some other useful features of psql, which are as follows:

  • Information functions

  • Output formatting

  • Execution timing using the \timing command

  • Input/output and editing commands

  • Automatic startup files, such as .psqlrc

  • Substitutable parameters (variables)

  • Access to the OS command line