Ways to Retrieve Data From PostgreSQL Hstore

Developing programming and coding technologies working in a software engineers.
Reading Time: 3 minutes

Postgre without a doubt is one of the most popular databases in the market. The reason behind this is speed, security, and robustness. One of the reasons for its popularity is that it has many amazing features. In this blog, we are going to discuss one of its important features which is hstore. Here we will see how we can retrieve data from hstore column. If you want to know How To Create And Insert Data In Postgre Hstore then check this blog => https://blog.knoldus.com/how-to-create-and-insert-data-in-postgre-hstore/

If you want to know about the basics of PostgreSQL then you can follow these blogs:

  1. Introduction to PostgreSQL
  2. CRUD Operations
  3. Hstore – How to create and insert data
  4. Comparison with Cassandra.
postgre image

Retrieve data from hstore column in Postgre

For knowing how to retrieve data from PostgreSQL Hstore let us take help from the below table. Let us assume that the name of the table is employee, in this table id store the employee id, name stores the name of the employee and details is hstore which store department, city and manager name of the employee.

Postgre table for example

Now we will assume a few cases and will try to write queries for them.

Case 1: Retrieving all the data stored in the hstore. In this case, we have to print all the data stored in detail (hstore) from the employee table

Query ->

Output ->

Case 2: Retrieving data from the specific keys. Suppose we have to print the employee name (name) and the city where they belong (inside details key-> city) from the employee table

Query ->

Output ->

Case 3: Using hstore data value in the WHERE clause. Suppose we have to print the city of the employee whose id is 1.

Query ->

Output ->


We get to know a few queries which can help us to retrieve data from Postgres hstore element.

Written by 

Divyansh Devrani is a Software Consultant having experience in the Scala Ecosystem. He has worked on technologies like Scala, Akka, Play, and Postgre. He is recognized as a good team player who wants to interact with new technologies. He loves to learn new things His hobbies include listening to muisc, watching movies and anime.