Peeping into PostgreSQL

April 15, 2009, 1:18 PM — In today's column, we're going to look at some useful scripts for getting information from your PostgreSQL databases. We'll put some SQL queries into use in the form of sql scripts and see how easily we can ask questions about the content of tables, the size of our databases and such.

To begin with, let's create a script that lists all the tables in the database. The PostgreSQL command to list tables is simply "\dt". If we put this command in a file and call it listTables.sql, we can then run the script like this:

% psql -f listTables.sql
                List of relations
 Schema |         Name         | Type  |  Owner
--------+----------------------+-------+----------
 public | alarm_status         | table | postgres
 public | accounts             | table | postgres
 public | filters              | table | postgres
...

You can run this command from the point of view of a particular user by adding "-U username" to the command. For example:

% psql -U privuser -f listTables.sql

If you don't want to go to the trouble of stashing your little "\dt" command in a script (maybe it's not all that hard to remember?), you can offer it as an argument to psql like this:

% psql -c "\dt"
                List of relations
 Schema |         Name         | Type  |  Owner
--------+----------------------+-------+----------
 public | alarm_status         | table | postgres
 public | accounts             | table | postgres
 public | filters              | table | postgres
...

If you're curious about database tables, you might also want to get a description of a particular table using the "\d" (describe) command:

% cat descTable.sql
\d addressbook
% psql -f descTable.sql
             Table "addressbook"
     Column     |         Type          | Modifiers
----------------+-----------------------+-----------
  name          | character varying(32) | not null
  addr          | character varying(48) |

Again, you could also just include your SQL command as an argument to psql:

% psql -c "\d addressbook"
              Table "addressbook"
     Column     |         Type          | Modifiers
----------------+-----------------------+-----------
  name          | character varying(32) | not null
  addr          | character varying(48) |

To determine how many records are in some particular database table, you could create a query that uses COUNT to tally up the records like this:

SELECT COUNT(*) FROM mytable;

You might call the script containing this query "rowcount.sql" and run it like this:

% psql -f rowcount.sql

Of course, it's probably much more useful to be able to ask about the size of any arbitrary table, so it might be more useful to create your sql file on the fly using a simple script like this:

#!/bin/bash

if [ $# == 0 ]; then
    echo -n "table> "
    read T
else
    T=$1
fi

echo "SELECT COUNT(*) FROM $T" > query.sql

psql -f query.sql

The script will create a query.sql file for any table you specify.

% ./getTableSize alarms
 count
-------
    43
(1 row)

If you want to see the size of the entire database, you can put a more complex query together. This one you probably don't want to memorize!

% cat getsize.sql
SELECT datname,pg_size_pretty(pg_database_size(oid)) FROM pg_database ORDER BY pg_database_size(oid) DESC;

When we run this query, we get a listing of the databases and their sizes as shown here:

% psql -f ./getsize.sql
  datname  | pg_size_pretty
-----------+----------------
 A         | 962 MB
 postgres  | 3697 kB
(2 rows)

Any of these commands could also be run by starting psql and issuing them at the prompt as shown below, but then you have to remember the right commands or have them ready to cut and paste into your terminal window. Saving them as scripts makes it easier to ask the questions you might want to ask every now and then.

% psql
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
A=# SELECT datname,pg_size_pretty(pg_database_size(oid)) FROM pg_database ORDER BY 

pg_database_size(oid) DESC;
  datname  | pg_size_pretty
-----------+----------------
 A         | 642 MB
 postgres  | 3697 kB
 template1 | 3697 kB
 template0 | 3697 kB
(4 rows)
A=# \dt
                  List of relations
 Schema  |          Name           | Type  |  Owner
---------+-------------------------+-------+----------
 public  | alarm_status         | table | postgres
 public  | accounts             | table | postgres
 public  | filters              | table | postgres
...

If you run SQL commands all of the time, you might be conversant enough to issue all the commands you need without turning them into scripts. If you're an occassional SQL user, on the other hand, you might find that entrusting the most useful commands to scripts is a good way to go.