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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.