psql -U postgres
psql -d mydb -U myuser -W
psql -h myhost -d mydb -U myuser -W
psql -U myuser -h myhost "dbname=mydb sslmode=require" # ssl connection

sudo -u postgres psql -U postgres
sudo -u postgres psql

select * from pg_tables where tableowner = 'postgres';

alter user usertochange with password 'new_passwd';

psql -W -U username -H hostname < file.sql

pg_dump -W -U username -h hostname database_name > file.sql

SELECT nextval('my_id_seq');

CREATE USER lemmy WITH PASSWORD 'myPassword';# orsudo -u postgres createuser lemmy -W

ALTER USER Postgres WITH PASSWORD 'mypass';

ALTER USER myuser WITH createdb;

create user mysuper with password '1234' SUPERUSER
# or even better
create user mysuper with password '1234' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN REPLICATION;# orsudo -u postgres createuser lemmy -W -s

alter user mysuper with superuser;# or even better
alter user mysuper with SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN REPLICATION

alter database database_name owner to new_owner;

CREATE DATABASE newdb WITH TEMPLATE originaldb;

SELECT * FROM pg_stat_activity;

show all;select * from pg_settings;

# show block_size;
 block_size
------------
 8192(1 row)

SELECT prosrc FROM pg_proc WHERE proname = 'procname'

# readonly to all tables for myusergrant select on all tables in schema public to myuser;# all privileges on table1 and table2 to myusergrant all privileges on table1, table2, table3 to myuser;

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d mydb latest.dump

# Postgres 9.6 and aboveSELECT pg_terminate_backend(pg_stat_activity.pid)FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB'AND pid <> pg_backend_pid();# Postgres 9.6 and belowSELECT pg_terminate_backend(pg_stat_activity.procpid)FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB'AND procpid <> pg_backend_pid();

-- List procedure/functionSELECT * FROM pg_proc WHERE proname='__procedurename__';-- List view (including the definition)SELECT * FROM pg_views WHERE viewname='__viewname__';-- Show DB table space in useSELECT pg_size_pretty(pg_total_relation_size('__table_name__'));:

-- Show DB space in useSELECT pg_size_pretty(pg_database_size('__database_name__'));-- Show current user's statement timeoutshow statement_timeout;-- Show table indexesSELECT * FROM pg_indexes WHERE tablename='__table_name__' AND schemaname='__schema_name__';-- Get all indexes from all tables of a schema:SELECT
   t.relname AS table_name,
   i.relname AS index_name,
   a.attname AS column_name
FROM
   pg_class t,
   pg_class i,
   pg_index ix,
   pg_attribute a,
   pg_namespace n
WHERE
   t.oid = ix.indrelid
   AND i.oid = ix.indexrelid
   AND a.attrelid = t.oid
   AND a.attnum = ANY(ix.indkey)AND t.relnamespace = n.oid
   AND n.nspname = 'kartones'ORDER BY
   t.relname,
   i.relname

-- Queries being executed at a certain DBSELECT datname, application_name, pid, backend_start, query_start, state_change, state, query
  FROM pg_stat_activity
  WHERE datname='__database_name__';-- Get all queries from all dbs waiting for data (might be hung)SELECT * FROM pg_stat_activity WHERE waiting='t';

-- See the query plan for the given query
EXPLAIN __query__

-- See and execute the query plan for the given query
EXPLAIN ANALYZE __query__

-- Collect statistics
ANALYZE [__table__]

-- Query data in columns c1, c2 from a table
SELECT c1, c2 FROM t;

-- Query distinct rows from a table
SELECT DISTINCT c1
FROM t
WHERE condition;

-- Sort the result set in ascending or descending order
SELECT c1, c2
FROM t
ORDER BY c1 ASC [DESC];

-- Skip offset of rows and return the next n rows
SELECT c1, c2
FROM t
ORDER BY c1
LIMIT n
OFFSET offset;

-- Group rows using an aggregate function
SELECT c1, aggregate(c2)
FROM t
GROUP BY c1;

-- Filter groups using HAVING clause
SELECT c1, aggregate(c2) FROM t
GROUP BY c1
HAVING condition;

-- Inner join t1 and t2SELECT c1, c2
FROM t1
INNER JOIN t2
ON condition;-- Left join t1 and t1SELECT c1, c2
FROM t1
LEFT JOIN t2
ON condition;-- Right join t1 and t2SELECT c1, c2
FROM t1
RIGHT JOIN t2
ON condition;-- Perform full outer joinSELECT c1, c2
FROM t1
FULL OUTER JOIN t2
ON condition;-- Produce a Cartesian product of rows in tablesSELECT c1, c2
FROM t1
CROSS JOIN t2;-- Another way to perform cross joinSELECT c1, c2
FROM t1, t2;-- Join t1 to itself using INNER JOIN clauseSELECT c1, c2
FROM t1 A
INNER JOIN t2 B ON condition

-- Combine rows from two queriesSELECT c1, c2 FROM t1
UNION [ALL]SELECT c1, c2 FROM t2;-- Return the intersection of two queriesSELECT c1, c2 FROM t1
INTERSECTSELECT c1, c2 FROM t2;-- Subtract a result set from another result setSELECT c1, c2 FROM t1
EXCEPTSELECT c1, c2 FROM t2;-- Query rows using pattern matching %, _SELECT c1, c2 FROM t1
WHERE c1 [NOT] LIKE pattern;-- Query rows in a listSELECT c1, c2
FROM t
WHERE c1
[NOT] IN value_list;-- Query rows between two valuesSELECT c1, c2
FROM t
WHERE c1
BETWEEN low AND high;-- Check if values in a table is NULL or notSELECT c1, c2 FROM t
WHERE c1 IS [NOT] NULL;