Using JSON dataype for database queries – PostgreSQL

I was torn between using relational database and document database like mongodb, elastic search etc for certain requirement. In fact I started learning elasticsearch but getting some of the tasks done with elasticseach is very painful compared to how easy its done in relational database. This was impacting my timeline and I was spending way too much time doing troubleshooting.

This is when I came across json datatype in PostgreSQL. This is combinations of easiness of relational database and document query capabilities of elastic seach. Of course this would be bit slower than elasticseach (I have not benchmarked the performance, buts its guess) but it is OK as I am in prototype phase, I just need to validate my business case, once it flies, I will make a switch be relevant document database.

Meanwhile, lets us see how to work with JSON datatype of PostgreSQL

select clause

Because -> operator returns a JSON object, you can chain it with the operator ->> to retrieve a it in text format.

SELECT id, productid,  productdetails -> 'productBaseInfoV1' ->> 'productUrl' AS customer
FROM public.ecomm_productdetails;
SELECT id, productid,  productdetails -> 'productBaseInfoV1' -> 'productUrl' AS customer
FROM public.ecomm_productdetails;

where clause

Please note you always need to use final element with ->> since this needs to be matched with text field.

SELECT id, productid,  productdetails
FROM public.ecomm_productdetails
WHERE productdetails -> 'productBaseInfoV1' ->> 'title' = 'Apple iPhone 6 (Grey, 128 GB)';


SELECT id, productid,  productdetails
FROM public.ecomm_productdetails
WHERE productdetails -> 'productBaseInfoV1' ->> 'title' like 'OPPO%';

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=#\connect dbname

To create table and user

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';

To exit from command prompt

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

Creating new user.


further details here

Change user password


making a user as 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

    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;

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  


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: