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  

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;

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

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;

Taking back up of PostgreSQL db and loading back up file

I am working with huge data set and its growing everyday. Fear of loosing data which you have gathered after so much efforts can give you sleepless nights. One of the way to tack care of this risk is by taking backup frequently, whenever you need reload the data, you can simply reload it.

Creating backup file
pg_dump dbname > outfile  
Loading from back up file
psql dbname < infile  

please note that dbname needs to be created prior to loading this backup file and it will not only create database in the same format but also it will load data.

Dealing with huge datasets

If your are dealing with huge databses you can takke dump in zip file

pg_dump dbname | gzip > filename.gz  

and load it as below

gunzip -c filename.gz | psql dbname  

or

cat filename.gz | gunzip | psql dbname  

alternatively you can split files if you are not comfortable with zip or have limitation of file size.

pg_dump dbname | split -b 1m - filename  

load with

cat filename* | psql dbname  

How to install pgAdmin on Ubuntu 16.04

pgadmin3 can be installed using following command

sudo apt-get install pgadmin3

You can invoke pgadmin3 GUI from main menu as well as by typing following command.

$ pgadmin3

From file menu, please use ‘add server’ and enter data as below:

If you facing authentication issue, please use following commands to reset the password.

$sudo -u postgres psql postgres
psql (9.6.3)  
Type "help" for help.

postgres=# password postgres  
Enter new password:  
Enter it again: