Rodrigo de Avila

Useful PostgreSQL Commands

Running Long Scripts Inside a Transaction

BEGIN;
\o /data/file.log
\i /data/file.sql
COMMIT;   # If no errors
ROLLBACK; # If you have problems
\o # Back to terminal

Restore Backup Inside a .tar.bz2 File

Search for the .out file inside the .tar.bz2 file.

tar --list --file=backup.tar.bz2

This command lists all files inside the compressed file.

script.sh
README.txt
backup/my_db.out
backup/my_db_log_backup.txt

Now, create the new database.

createdb my_db

Use the correct .out path (inside the .tar.bz2) in the tar command, to the stdout. At the same time, send the stdout to psql.

tar Ojxvf backup.tar.bz2 backup/my_db.out | psql my_db

Show NULL Fields on psql

Normally, the psql command leaves the null fields blank when you run a query. To force psql to show the null values, use the \pset command.

rodrigo@server:~$ psql test_db
psql (9.1.11)
Type "help" for help.

test_db=# SELECT NULL;
 ?column?
----------

(1 row)

test_db=# \pset null '(null)'
Null display is "(null)".
test_db=# SELECT NULL;
 ?column?
----------
 (null)
(1 row)

test_db=# \q
rodrigo@server:~$

In the example above, we see how PostgreSQL shows the null value before and after the \pset command. The chosen text to represent the null value was (null), but it can be any other string.

This configuration is available only in this psql execution.

Reference: Output Format Options