PostgreSQL Quick Reference

Check which version you are running:

$ psql --version
psql (PostgreSQL) 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1)

Creating new user.

CREATE USER davide WITH PASSWORD 'jw8s0F4';  

further details here

Change user password

ALTER USER admin WITH PASSWORD 'admin';  

making a user as superuser

ALTER USER username WITH SUPERUSER;

further details here

check the list of users:

select u.usename,  
       (select string_agg(d.datname, ',' order by d.datname) 
        from pg_database d 
        where has_database_privilege(u.usename, d.datname, 'CONNECT')) as allowed_databases
from pg_user u  
order by u.usename  

Alter table and add column

ALTER TABLE public.nseequitydata 
ADD COLUMN splitfactor bigint NOT NULL DEFAULT 1;

Login to postgreSQL

sudo su - postgres
[sudo] password for conquistador: 
postgres@inspiron-3542:~$ psql
postgres=#
postgres=#\connect dbname

Important commands

Activity	Command
Show all users	postgres=# \du
Show all databases	postgres=# \l
Connect to database	postgres=# \connect equityindia
Show tables	equityindia=# \dt
Show tables details	equityindia=# \d
Show to remote postgresql database	psql -h host -d database -U user -W
Show views	equityindia=# \dv
Show functions	equityindia=# \df
Command History	equityindia=# \s