PostgreSQL Quick Notes

Check which version you are running: ```bash $ psql --version psql (PostgreSQL) 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1) ``` ## Login to psql ```bash $ sudo su - postgres $ psql postgres=# ``` ## Logout from psql To exit from command prompt ```bash databasename-# \q postgres@ubuntu:~$ exit logout username@ubuntu:~$ ``` ## User management Creating new user. ```sql CREATE USER davide WITH PASSWORD 'jw8s0F4'; ``` Change user password ```sql ALTER USER admin WITH PASSWORD 'admin'; ``` Making a user as superuser ```sql ALTER USER username WITH SUPERUSER; ``` Sometimes, you need to check which all users are created in database, simple run following command ```sql 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 ```sql 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
PostgreSQL