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