useful postgresql commands

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 list 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 leave the null fields blank, when you run a query. to force psql to showthe null values, use the \pset command.

[email protected]:~$ 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
[email protected]:~$

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 can be any other string.

this configuration is available only in this psql execution.

reference: Output Format Options