PostgreSQL Quick Reference

Check which version you are running:

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

Login to postgreSQL

sudo su - postgres
postgres@inspiron-3542:~$ psql
postgres=#
postgres=#\connect dbname

To create table and user

CREATE DATABASE mydbname;
CREATE DATABASE myuser;
CREATE USER myuser WITH PASSWORD 'myuser';  
ALTER ROLE myuser SET client_encoding TO 'utf8';
ALTER ROLE myuser SET default_transaction_isolation TO 'read committed';
ALTER ROLE myuser SET timezone TO 'UTC';
GRANT ALL PRIVILEGES ON DATABASE mydbname TO myuser;

To exit from command prompt

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

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  

Creating a table

CREATE TABLE equity (
    symbol character(20),
    timestamp TIMESTAMP,
    open numeric,
    high numeric,
    low numeric,
    close numeric,
    volume INT,
    average numeric,
    barCount numeric
    --UNIQUE (symbol, timestamp)
);

Alter table and add column

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

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

check the size of tables

 

SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a
) a;