Note: the commands were tested on Postgres 9.5.4
Connect with the user USER_NAME
psql -h REMOTE_SERVER_ADDRESS -U USER_NAMEMore options
-W: to force psql to prompt for a password"sslmode=require": Connect in ssl mode
If executed with -E flag, it will describe the underlaying queries of the run \ commands.
\l: List databases\q: Exit psql\hor\?: Show help\c <database_name>: Connect to a database\d: List the database relations\d <table_name>: Sho the table definition\dt: List all database tables\du: List users and their privileges for the current database\z <table_name>or\dp <table_name>: Get information about table privileges\timing on|ofTurn on/off the timing of the queries\l+Show database sizes\d+Show tables sizes\dti+Show both tables and indexes' sizes Useful Queries
List Tables
SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema')Show psql version
SELECT version();List user privileges for a specific table
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name='table_name';And grouped by grantee
SELECT grantee, string_agg(privilege_type, ', ') AS privileges
FROM information_schema.role_table_grants
WHERE table_name='table_name'
GROUP BY grantee;Export Query output to a CSV file
COPY (your_query ) TO '/tmp/file_name.csv' WITH CSV HEADER;For more options see PostgreSQL COPY documentation or the handy command line tool psql2csv
List current user
SELECT current_user;List current database
SELECT current_database();Show all available columns descriptions
SELECT c.table_schema,c.table_name,c.column_name,pgd.description
FROM pg_catalog.pg_statio_all_tables as st
inner join pg_catalog.pg_description pgd on (pgd.objoid=st.relid)
inner join information_schema.columns c on (pgd.objsubid=c.ordinal_position
and c.table_schema=st.schemaname and c.table_name=st.relname);Show all table's columns descriptions
SELECT
cols.column_name,
(
SELECT
pg_catalog.col_description(c.oid, cols.ordinal_position::int)
FROM
pg_catalog.pg_class c
WHERE
c.oid = (SELECT ('"' || cols.table_name || '"')::regclass::oid)
AND c.relname = cols.table_name
) AS column_comment
FROM
information_schema.columns cols
WHERE
cols.table_catalog = 'database_name'
AND cols.table_name = 'table_name'
AND cols.table_schema = 'schema_name';Get available settings params containing par
SELECT name,setting FROM pg_settings WHERE name LIKE '%par%';Get table size in disk doc
SELECT pg_size_pretty(pg_total_relation_size('schema.Table'));Show stats of all current activities in the databases
SELECT datname, pid, query from pg_stat_activity ;Show the plan that the query will use
EXPLAIN SELECT * FROM table_name;Get day name from current date
select to_char(current_date, 'day');Get day number number from current date
SELECT extract(dow from current_date);Show day part from current_date
SELECT extract(day from current_date);- yesterday
- today
- tomorrow
- now
- today
SELECT TIMESTAMP 'today'e.g:
SELECT column_name from table_name where time < TIMESTAMP 'today';
SELECT column_name from table_name where time < now() - interval '24 hour';Get day or timestamp from date string
SELECT '2017-01-19'::timestamp;timestamp
---------------------
2017-01-19 00:00:00
SELECT '2017-01-19'::date;date
--------------
2017-01-19
Show timestamp in different timezone
SELECT to_char('2 2017-01-19 15:00 CST6CDT'::timestamptz
, 'YYYY-MM-DD HH24:MI:SS TZ') to_char
--------------------------
2017-01-19 22:00:00 CEST
Switch the local timezone to the desired display timezone, for the duration of the transaction:
Note: the local zone should be set using the full time zone name instead of the abbreviation. Check pg_timezone_names table for the names/abbreviation mapping.
BEGIN;
SET local timezone TO 'EST5EDT';
SELECT to_char('2017-01-19 10:00:00'::timestamp AT time zone 'CDT',
'YYYY-MM-DD HH24:MI:SS TZ');
END;
Grant SELECT privilege on all database tables for a specific user
GRANT CONNECT ON DATABASE <database_name> to <user_name>;
\c database_name
GRANT USAGE ON SCHEMA public to <user_name>;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO <user_name>;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user_name>;Revoke permission
REVOKE <permission_type> ON <table_name> FROM <user_name>;