Few queries that might help you in PostgreSQL

Image source — en.wikipedia.org

It’s been 2 years since I am working as a QA(Quality Assurance) engineer by profession in an MNC. I am a manual tester and I do functional and database level testing. I called myself a learner because I am still in the growing stage, there are many things which I am unaware of. When I was in college, I had zero ideas that PostgreSQL does exists. I have stuck many times and, I am shamelessly saying that I have taken the help of google, sometimes google wasn’t a great help for me. So, I had to do brainstorming all by myself. Here, I am sharing a few helpful queries related to PostgreSQL DB, which has helped me a lot and I hope that it will definitely help someone else too.

PostgreSQL — Data is stored in JSON format, in simpler terms data is stored in the form of an object in the JSON document. In Oracle and SQL Server DBs, data are stored in the form of rows and columns, so, fetching data is easier as compared to PostgreSQL DB. In PGSql, the user has to call the object to fetch the records from the JSON document.

Mentioned below are a few of the helpful queries that I used while fetching records. Kindly excuse me if I misspelled any literals, my purpose is to share the tips that I follow, so again excuse me if I have incorrectly mentioned the names.

Created by author
  1. Fetching data from josndoc
Created by author

This is a basic query that is used to fetch records present in the JSON doc. With the help of pointers or the array, the user can address the object which he wants to fetch the records of.

2. Fetching sub child

Created by author

Suppose the user wanted to fetch the highlighted records, here, Origination is an object which is having records of 3 objects. To fetch the records, the user can use the following query -

Created by author

3. Fetching data from jsonb array elements

Created by author

I have struggled in this query because earlier I didn’t have any idea how to fetch records from jsonb array elements. So to get the correct result, I created an instance of the jsonb array element, and then I called the objects. This has worked for me and has literally saved my time.

4. Sorting the data

Since the data in PostgreSQL are not stored in the form of rows and column, so when a user applies sorting check, then DB do not give the appropriate result. For example, if a user applied has applied a sorting condition on the ID column (let say it exists on DB) and the data present in ID columns start with 1 to 100.

The result will be like this — 1,10,100, 2, 20,200….

As you can see, the DB is sorting data but not in the right order that we want. To avoid this, the user has to use cast condition

Created by author

These are the few helpful queries that are a game-changer for me. It took some time for me to understand the basic concept and to create these queries by tons of hit and trial efforts. I am still learning, I still get stuck while applying Joins. But I believe that that’s how you learn, once you get stuck and you try to find various approaches, after so many failed attempts and frustration, you finally came across to the desired query. That’s how success works, you fail, you learn, you grow and then you share your learning with others.



Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store