##Postgres commands cheatsheet
#####Setup data directory for database
initdb -D /usr/local/pgsql/data
#####Reload Postgres config from command line:
select pg_reload_conf();
#####List databases
postgres=# \l
#####List roles
postgres=# \du
#####Create a new role
postgres=# CREATE ROLE demorole1 WITH LOGIN ENCRYPTED PASSWORD 'password1' CREATEDB;
#####Create database
postgres=# CREATE DATABASE demodb1 WITH OWNER demorole1 ENCODING 'UTF8’;
#####Grant privileges to new user
GRANT ALL PRIVILEGES ON DATABASE demodb1 TO demorole1;
#####Drop database
postgres=# DROP DATABASE demodb1;
#####Connect to database
postgres=# \c <databasename>
#####List tables in connected database
postgres=# \dt
#####Describe table with indexes included
postgres=# \d <tablename>
#####Backup database
pg_dump dbname > backup
or
pg_dump dbname | gzip backup.gz
#####Restore database from backup
psql dbname < backup
or
gunzip -c backup.gz | psql dbname
or
psql --single-transaction dbname < backup
#####Backup cluster
pg_dumpall > backup (includes roles as well)
#####Restore cluster
psql -f backup dbname
#####Send results of query to file
\copy (SELECT * from weather) TO '~/Desktop/weather.csv'
#####Execute commands from a file
psql -U username -d database_name -a -f filename.sql
#####Extracting a particular param from a timestamp
SELECT extract(month from timestamp '2001-02-17 02:08:40+05:30');
#####Get current date/time
SELECT (CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP | LOCALTIME | LOCALTIMESTAMP);
#####Finding difference b/w two dates/timestamps
select age(now(), '2014-01-01’);
#####Using BETWEEN as comparison operator
select * from weather where high BETWEEN 40 AND 48;
(equivalent to high >=40 AND high <= 48)
#####Retrieve values from a JSON structure
select '{"a":1,"b":221233}'::json->>'b';
select '{"a":1,"b":[1,2,3]}'::json#>'{b,0}’;
#####Retrieve each row of a table as JSON
select row_to_json(weather.*) from weather;
#####Get the JSON aggregate of all rows in a table
select json_agg(weather.*) from weather;
#####Retrieve particular value from JSON using key
select json_extract_path('{"city":"San Francisco","low":43,"high":51,"date":"1994-11-29"}','date’)
#####Check the existence of elements in array
SELECT ARRAY[1,2,55,79] <@ ARRAY[1,2,34,3,455,45,6,7,22,33,11,23,55,666,09,24,26,28,98,79,67];
#####Get the current user
select current_user;
#####Get the current query being executed
select current_query();
#####Get the disk space used by a particular database
select pg_database_size(dbname); -- in bytes
#####Number of bytes used to store a column value
select pg_column_size(column_name) from table;
#####Force Index Scan (Use with caution!)
SET enable_seqscan TO off;
#####Get number of rows in table, it’s indexes and the disk pages used
SELECT relname, relkind, reltuples, relpages
FROM pg_class
WHERE relname LIKE ’tname%';
#####Get some statistics about a table
SELECT attname, inherited, n_distinct,
array_to_string(most_common_vals, E'\n') as most_common_vals
FROM pg_stats
WHERE tablename = ’tname';
###Index and Cache Hit Rate of all tables in a database
-- Index hit rate
WITH idx_hit_rate as (
SELECT
relname as table_name,
n_live_tup,
round(100.0 * idx_scan / (seq_scan + idx_scan),2) as idx_hit_rate
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC
),
-- Cache hit rate
cache_hit_rate as (
SELECT
relname as table_name,
heap_blks_read + heap_blks_hit as reads,
round(100.0 * sum (heap_blks_read + heap_blks_hit) over (ORDER BY heap_blks_read + heap_blks_hit DESC) / sum(heap_blks_read + heap_blks_hit) over (),4) as cumulative_pct_reads,
round(100.0 * heap_blks_hit / (heap_blks_hit + heap_blks_read),2) as cache_hit_rate
FROM pg_statio_user_tables
WHERE heap_blks_hit + heap_blks_read > 0
ORDER BY 2 DESC
)
SELECT
idx_hit_rate.table_name,
idx_hit_rate.n_live_tup as size,
cache_hit_rate.reads,
cache_hit_rate.cumulative_pct_reads,
idx_hit_rate.idx_hit_rate,
cache_hit_rate.cache_hit_rate
FROM idx_hit_rate, cache_hit_rate
WHERE idx_hit_rate.table_name = cache_hit_rate.table_name
AND cumulative_pct_reads < 100.0
ORDER BY reads DESC;