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:
- Introduction to PostgreSQL
- CRUD Operations
- Hstore – How to create and insert data
- Comparison with Cassandra.
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.
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 ->
CONCLUSION
We get to know a few queries which can help us to retrieve data from Postgres hstore element.
