PostgreSQL Quick Notes

Check which version you are running:

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

Login to psql

$ sudo su - postgres
$ psql
postgres=#

Logout from psql

To exit from command prompt

databasename-# \q
postgres@ubuntu:~$ exit
logout
username@ubuntu:~$

User management

Creating new user.

CREATE USER davide WITH PASSWORD 'jw8s0F4';

Change user password

ALTER USER admin WITH PASSWORD 'admin';

Making a user as superuser

ALTER USER username WITH SUPERUSER;

Sometimes, you need to check which all users are created in database, simple run following command

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;

List of 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