Postgres Commands Cheat Sheet

sudo service postgresql stop
sudo service postgresql start
sudo service postgresql restart
sudo systemctl restart postgresql
                    

SELECT datname, application_name, pid, backend_start, query_start, state_change, state, query 
  FROM pg_stat_activity 
  WHERE datname='__database_name__';
                

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
                

SELECT * FROM pg_stat_activity WHERE waiting='t'
                   

SELECT 
  pg_stat_get_backend_pid(s.backendid) AS procpid, 
  pg_stat_get_backend_activity(s.backendid) AS current_query
FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
                   

SELECT datname, numbackends FROM pg_stat_database;
                   

INSERT INTO some_table (a_float_value) SELECT random() * 100000 FROM generate_series(1, 1000000) i;        
    

sudo su - postgres
psql
                   

GRANT ALL PRIVILEGES ON DATABASE  TO ;
                        

GRANT USAGE ON SCHEMA public TO ;
                     

GRANT CONNECT ON DATABASE  TO ;
                     

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO ;
                     

GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO ;
                     

GRANT SELECT, UPDATE, INSERT ON <table_name> TO ;
                     

GRANT SELECT ON ALL TABLES IN SCHEMA public TO ;
                     

SELECT * FROM <table_name>;
                   

SELECT * FROM <table_name> LIMIT 1;
                    

SELECT * FROM <table_name> WHERE <column_name> = <value>;
                     

INSERT INTO <table_name> VALUES( <value_1>, <value_2> );
                     

UPDATE <table_name>
SET <column_1> = <value_1>, <column_2> = <value_2>
WHERE <column_1> = <value>;
                     

DELETE FROM <table_name>;
                     

DELETE FROM <table_name>
WHERE <column_name> = <value>;
                     

EXPLAIN query;
                   

EXPLAIN ANALYZE query;
                    

ANALYZE table_name;
                     

  SELECT current_database() as database,
  pg_size_pretty(total_database_size) as total_database_size,
  schema_name,
  table_name,
  pg_size_pretty(total_table_size) as total_table_size,
  pg_size_pretty(table_size) as table_size,
  pg_size_pretty(index_size) as index_size
  FROM ( SELECT table_name,
          table_schema as schema_name,
          pg_database_size(current_database()) as total_database_size,
          pg_total_relation_size(table_name) as total_table_size,
          pg_relation_size(table_name) as table_size,
          pg_indexes_size(table_name) as index_size
          FROM information_schema.tables
          WHERE table_schema=current_schema() and table_name like 'table_%'
          ORDER BY total_table_size
      ) as sizes;        
    

COPY table_name [ ( column_name [, ...] ) ]
FROM { 'filename' | STDIN }
[ [ WITH ] ( option [, ...] ) ]

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
TO { 'filename' | STDOUT }
[ [ WITH ] ( option [, ...] ) ]        
    

SELECT table_catalog, table_schema, table_name, privilege_type
FROM   information_schema.table_privileges
WHERE  grantee = 'user_to_check' ORDER BY table_name;        
    

SELECT
    r.rolname,
    r.rolsuper,
    r.rolinherit,
    r.rolcreaterole,
    r.rolcreatedb,
    r.rolcanlogin,
    r.rolconnlimit,
    r.rolvaliduntil,
    ARRAY(SELECT b.rolname
      FROM pg_catalog.pg_auth_members m
      JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
      WHERE m.member = r.oid) as memberof, 
    r.rolreplication
FROM pg_catalog.pg_roles r
ORDER BY 1;
    

SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name='name-of-the-table';
    

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = current_database() AND pid <> pg_backend_pid();
            

ALTER TABLE <table_name> IF EXISTS
ADD <column_name> <data_type> [<constraints>];
                   

ALTER TABLE <table_name> IF EXISTS
ALTER <column_name> TYPE <data_type> [<constraints>];
                        

ALTER TABLE <table_name> IF EXISTS
DROP <column_name>;
                     

ALTER TABLE <table_name>
ADD COLUMN <column_name> SERIAL PRIMARY KEY;
                     

INSERT INTO <table_name>
VALUES (DEFAULT, <value1>);


INSERT INTO <table_name> (<column1_name>,<column2_name>)
VALUES ( <value1>,<value2> );