If instead of a blank space you'd prefer something that tells you a particular column in a row is NULL, you can set it:

\pset null '(null)'

If you want to run a query or a function and ditch the output, you can do something like this:

xinu=# select my_void_function() \g /dev/null

If you want to see how a query like \du is being built:

\set ECHO_HIDDEN

If you want to tail the errors on another terminal, just push them to a fifo:

$ mkfifo pgerror
$ psql -U user dbname < ./dbfile 2> pgerror

On your other terminal:

$ tail -f pgerror

Voila! STDOUT on the main terminal, and STDERR on the secondary.

If you've munged your template1 database, you can rebuild it from the template0 database.

First, you must set the datistemplate attribute of template1 to false so we can drop it:

xinu=# UPDATE pg_database SET datistemplate = false WHERE datname = 'template1';
UPDATE 1
xinu=# DROP DATABASE template1;
DROP DATABASE

Next, re-create template1 using template0 as a template:

xinu=# CREATE DATABASE template1 WITH TEMPLATE = template0;
CREATE DATABASE

Then restore the datistemplate attribute of template1:

xinu=# UPDATE pg_database SET datistemplate = true WHERE datname = 'template1';
UPDATE 1

In your ~/.psqlrc, add this to emulate MySQL-style borders in query results:

\pset border 2

If you intend to use passwords for local database authentication, you'll need to make an adjustment to the pg_hba.conf file:

# TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK        METHOD

#local   all         all                                          ident sameuser
local   all         all                                          password

In 7.4 (and possibly 8.1), the routine to dump and restore the entire database cluster follows:

# su - postgres
$ pg_dumpall > postgresql.dmp

Transfer the postgresql.dmp file to the target system, and then do the import:

# su - postgres
$ psql template1 < postgresql.dmp

If you want to see how long a query is taking to run, you can either run explain analyze or enable timing for the client by typing \timing.

You can use RETURNS SETOF record in your plpgsql functions to return a set of rows. Here is an example of using pgsql to build a query using a table name parameter:

CREATE OR REPLACE FUNCTION test(text) RETURNS SETOF record AS '
DECLARE
  _table ALIAS FOR $1;
  _mycursor refcursor;
  _row record;
BEGIN
  OPEN _mycursor FOR EXECUTE ''SELECT * FROM '' || _table;
  FETCH _mycursor INTO _row;
  WHILE FOUND LOOP
    RETURN NEXT _row;
    FETCH _mycursor INTO _row;
  END LOOP;
  RETURN;
END
' LANGUAGE plpgsql;

Here is an example of calling such a function (the names and types of the result columns are required when returning SETOF record):

mydb=> SELECT * FROM test('mytable') AS (col1 integer, col2 text, col3 date);

Set the PROMPT1 variable using psql prompt escape sequences:

\set PROMPT1 '[%n@%M:%/]=%# '

• %n - User
• %M - Host ([local] or the domain or IP address of the server)
• %/ - Database
• %# - # if superuser, $ if regular user.

Set this variable in your ~/.psqlrc to stop on error when psql is used to run non-interactive scripts (e.g. cat file | psql ...):

\set ON_ERROR_STOP 1

Or use it from the command line:

$ psql ... -v ON_ERROR_STOP=1 ...