rdeavila's kb

Appearance

useful postgresql commands ​

running long scripts inside a transaction ​

sql
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.

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

this command list all files inside the compressed file.

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

now, create the new database.

bash
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.

bash
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.

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

this configuration is available only in this psql execution.

reference: Output Format Options