PostgreSQL commands cheatsheet
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 :)