A time has come and I need to work with PostgreSQL databases and as it always happens - some things concerning with this work need to be remembered. A best way of doing this is to write an article or, in my case, yet another cheat sheet.

So, here is a cheat sheet for working with PostgreSQL. It is not detailed at all and contains a few commands. And I guess they are useful when you have an experience writing SQL queries and want to know database-specific things. For more detailed cheat sheets there are a plenty good links at the bottom of this post.

Opening PostgreSQL console:

psql -h <hostname> -U <user> <dbname>

dbname is optional

Working with pgsql console

\?              - Help
\? <keyword>    - Help for SQL keyword
\du             - List roles
\l              - List databases
\c <db_name>    - Connect to database
\dt             - List tables (with selected DB)
\d
<table_name> - List columns in table
\di             - List all indexes for current schema
\q              - Exiting from console

Also, listing indexes for desired table, e.g. ‘products’:

select * from pg_indexes where tablename = 'products';

Dumping postgres database

pg_dump and pg_restore pair

pg_dump therrr-dev > dump.sql
pg_dumpall > dump.sql
# Example on dumping & restoring postgresql database
# Don't forget to 'CREATE DATABASE newdb;'
pg_dump -Ftar -fcp_dev.tar cp_dev
pg_restore -d newdb db.dump

Running a SQL script (command line)

psql -f script.sql databaseName

Some usefull sql commands syntax

-- comment to end of line
/* C-like comment, possibly
multiple lines */
-- Changing password for postgresql user fedor
ALTER USER fedor WITH PASSWORD 'fedor';

EXPLAIN ANALYZE

Except ordinary ANALYZE - for showing exact query benchmarking, i.e

cp_dev=# explain analyze SELECT "products".* FROM "products"
cp_dev-# WHERE (lower(name) like '%pre%' or lower(description) like '%pre%')
cp_dev-# ORDER BY name ASC LIMIT 100 OFFSET 100;
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=29995.24..29995.49 rows=100 width=2190) (actual time=1017.821..1017.832 rows=100 loops=1)
   ->  Sort  (cost=29994.99..30126.81 rows=52728 width=2190) (actual time=1017.809..1017.819 rows=200 loops=1)
         Sort Key: name
         Sort Method: top-N heapsort  Memory: 78kB
         ->  Seq Scan on products  (cost=0.00..27716.12 rows=52728 width=2190) (actual time=0.955..973.433 rows=20569 loops=1)
               Filter: ((lower((name)::text) ~~ '%pre%'::text) OR (lower(description) ~~ '%pre%'::text))
               Rows Removed by Filter: 651987
 Total runtime: 1018.692 ms
(8 rows)

Additions are welkome :)