Skip to main content

Postgres New JSON data type Support of Static and Dynamic schemas

Now we can create static and dynamic both kind of schema relational table so a new kind of heterogeneous data structure can be created in Relational tables.  Relational database that is much more concerned with standards compliance and extensible than with giving you freedom over how you store data. It uses both dynamic and static schema and allows you to use it for relational data and normalized form storage.

Static Relation data structure in Postgres

CREATE TABLE Project (  id serial PRIMARY KEY,  name varchar,  mgr integer,  is_active boolean );
CREATE TABLE Task(  id serial PRIMARY KEY, name varchar, status boolean, project_id integer,
CONSTRAINT idx_project_id FOREIGN KEY (project_id) REFERENCES project (id) );

Dynamic Relation data structure in Postgres

CREATE TABLE IF NOT EXISTS project ( id serial PRIMARY KEY,  name varchar,  mgr integer,  tasks jsonb , is_active boolean );

Keeping JSON Document inside Postgres Table

INSERT INTO project VALUES (1,'YoungDBA',2,'{"id": 1, "name": "test1","status": "Done","date": "2018-01-18", "is_active": "True"}'); 
INSERT INTO project VALUES (2,'YoungDBA',2,'{"id": 1, "name": "test1","status": "Done","date": "2018-01-18", "is_active": "True"}'); 
INSERT INTO project VALUES (3,'YoungDBA',2,'{"id": 1, "name": "test1","status": "Done","date": "2018-01-18", "is_active": "True"}');


Now aggregation from static and dynamic data is easier than than documentdb 

SELECT visitor_id, SUM(CAST(properties->>'amount' AS integer)) AS total
FROM events WHERE CAST(properties->>'amount' AS integer) > 0 GROUP BY visitor_id;

SELECT browser->>'name' AS browser, count(browser)
FROM events GROUP BY browser->>'name';

SELECT AVG(CAST(browser->'resolution'->>'x' AS integer)) AS width,
AVG(CAST(browser->'resolution'->>'y' AS integer)) AS height FROM events;


Thanks for reading
Plz dont forget to like Facebook Page..
https://www.facebook.com/pages/Sql-DBAcoin/523110684456757

Comments

  1. Thank you so much for throwing light on such an important topic, not sure if you are interested in 3rd party product but ZappySys has very easy solution.
    Link here"

    SSIS Postgresql Write

    ReplyDelete

Post a Comment

Plz dont forget to like Facebook Page..
https://www.facebook.com/pages/Sql-DBAcoin/523110684456757

Popular posts from this blog

How to encrypt and decrypt Table data in postgres

For encrypting and decrypting , we must use the bytea data type on the column which we implement. Bcoz bytea will use the pgcrypto method by default. However, you will need to create the pgcrypto extension to enable these functions as they are not pre-defined in PostgreSQL/PPAS. Example CREATE EXTENSION pgcrypto; CREATE TABLE userinfo (username varchar(20), password bytea); >>    Inserting the data in an encrypted format INSERT INTO userinfo VALUES(' suman ',encrypt('111222','password','aes')); select * from userinfo ; >>    Retrieving the data as decrypted format SELECT decrypt(password,decode('password','escape'::text),'aes'::text) FROM userinfo; Thanks for reading Plz dont forget to like Facebook Page.. https://www.facebook.com/pages/Sql-DBAcoin/523110684456757

How to recover msdb database from suspect mode

 It was Monday 9 th Jun 47 degr. temperature of Delhi-NCR. Temperature was like boiling me and database. When I reached my office( @ 8.45 am) got an alert from one of Server. “MSDB is in suspected mode” At the same time comes in my mind, this issue will boil me today.. I just tried to cool my self through cold drink then connected server from my local system using windows authentication mode..

History of MySQL from AB Corp to Cloud Database

MySQL was created by a Swedish company, MySQL AB, founded by David Axmark, Allan Larsson and Michael "Monty" Widenius. Original development of MySQL by Widenius and Axmark began in 1994. The first version of MySQL appeared on 23 May 1995. Its name is a combination of "My", the name of co-founder Michael Widenius's daughter,and "SQL", the abbreviation for Structured Query Language. ·          23 May 1995 - First internal release ·          Year 1996 - Version 3 o     Simple CRUD operations o     January 1997 Windows version was released on 8 January 1998 for Windows 95 and NT o     production release 1998, from www.mysql.com ·          Year 2002 - Version 4 o     MyISAM o     unions o     Tracking o     B-trees o     s...