In this section, we are going to understand the working of the PostgreSQL hstore data type, examples of the hstore data type, and we also see the example of the hstore with WHERE clause, which helps us to handle the hstore data values more resourcefully.
We will understand how we can add, update, deleting the key-values pairs in the PostgreSQL hstore data type.
We will discuss the following operators, such as -> Operator (to select the data for a specific value), ? Operator (to check for an explicit key in an hstore column), @> operator (to check the key-value pair), ?& and ?|Operator ( to select the rows whose hstore column is having various keys).
And also see the usage of avals(), svals(), akeys() skeys(), EACH(), hstore_to_json() functions.
What is PostgreSQL hstore Data Type?
In PostgreSQL, the next data type that we are going to understand is the hstore, which is used to store the key-value pair in the unit value. It is always the right approach for various cases, like semi-structured data or rows, with serval attributes, which are uncommonly enquired.
Note: Basically, the keys and values are the text strings.
Syntax of PostgreSQL hstore data type
The syntax of the PostgreSQL hstore data type is as follows:
variable_name hstore; Before we start learning the hstore data type, we must first allow the hstore extension, which loads the contrib module for our PostgreSQL illustration.
Enable PostgreSQL hstore extension
To enable the PostgreSQL hstore extension for our PostgreSQL design, we can use the CREATE EXTENSION command in the Organization database, as we can see in the following statement:
CREATE EXTENSION hstore; Output
After executing the above command, we will get the below message: the hstore extension has been installed successfully with the Create Extension command.
Example of PostgreSQL hstore data type
Let us see one sample example to understand how the PostgreSQL hstore data type works.
We are creating one new table as Movie and hstore columns with the CREATE command’s help and inserting some values using the INSERT command.
The Movie table contains the various columns such as Movie_id, Movie_name, Movie_attr and that have the following properties:
- The Movie_idis the primary key, which is used to find the movies.
- Movie_nameis other name of the Movies
- And the Movie_attrcolumn is used to contain the attributes of the Movies, for example, rating, movie_genres, language, running_time, and release year.
For the Movie_attr column, we have used the hstore data type.
To create a Movie table into a similar database which is Organization, where we enable the hstore extension, we use the CREATE command as we can see in the following command:
CREATE TABLE Movie (
Movie_id serial primary key,
Movie_name VARCHAR,
Movie_Attr hstore
); Output
On executing the above command, we will get the following message, which displays that the Movie table has been created successfully.
When the Movie table is created successfully, we will insert some values into it with the INSERT command’s help.
Inserting a value into PostgreSQL hstore column
To insert the value into the hstore column, we are using the INSERT command as shown in the below statement:
INSERT INTO Movie (Movie_name, Movie_attr)
VALUES
( 'Avengers Endgame',
'"rating" => "8.4",
"movie_genres" => "Action/Sci-fi",
"language" => "English",
"release_year" => "2019",
"running_time" => "181 Minutes"'
),
( 'US',
'"rating" => "6.9",
"movie_genres" => "Horror/Thriller",
"language" => "English",
"release_year" => "2019",
"running_time" => "116 Minutes"'
),
( 'Dolittle',
'"rating" => "5.6",
"movie_genres" => "Adventure/Family ",
"language" => "English",
"release_year" => "2020",
"running_time" => "101 Minutes"'
); Output
After implementing the above command, we will get the following message window, which displays that the specified values have been inserted successfully into the Movie table.
Note: As we can see in the above insert command, the values we inserted into the hstore column are a list of comma-separated key => value pairs. And both keys and values are quoted using double quotes (“).
After creating and inserting the values in the Movie table, we will use the SELECT command to retrieve all data from the Movie table:
Selecting values from an hstore column
Selecting the values from an hstore column is equal to selecting a value from a column with native data type with the help of the SELECT command, as shown below:
Select *
FROM Movie;Output
After successfully implementing the above command, we will get the below output, which displays all the data present in the Movie table:
Selecting data for a specific key
PostgreSQL hstore data type allows us to use the -> operator to select a particular key’s data values from an hstore column (Movie_Attr).
In the below example, we are using the -> operator to identify the running_time of all available movies in the Movie table, as shown in the below command:
SELECT
Movie_attr -> 'running_time' AS Time_duration
FROM
Movie;Output
We will get the following output after implementing the above command, which displays each movie’s running_time from the Movie table.
Using hstore data value in the WHERE clause
To filter the rows whose values of the hstore column match the input value. So, for this case, we can use the -> operator in the WHERE clause.
Let us see one sample example for our better understanding:
In the below example, we are trying to get Movie_name and Movie_genres of the movie, which has Relases_year value matches 2019:
SELECT
Movie_name, Movie_attr -> 'movie_genres' AS Genres
FROM Movie
WHERE Movie_attr -> 'release_year' = '2019'; Output
After implementing the above command, we will get the value of the hstore column using the WHERE clause in the output, as shown below:
Retrieve all values from an hstore column
To retrieve all the data values from the hstore column, we can use the avals() function in arrays.
In the following example, we are getting all the values from an hstore column in the Movie table with the help of the avals() function:
SELECT avals (Movie_attr)
FROM Movie; Output
After executing the above command, we will get the following output, which shows all the Moive_attr values available in the Movie table:
OR
If we want to retrieve the output as a set, we can use the svals() function.
In the following command, we are using the svals() function instead of avals() to get the output as a set:
SELECT svals (Movie_attr)
FROM Movie; Output
On implementing the above command, we will get the following output as a set:
Retrieve all keys from an hstore column
Like we can get all the values from the hstore column using avals() and svals() function, we can use the akeys() function for retrieving all keys from an hstore column:
SELECT akeys (Movie_attr)
FROM Movie; Output
On implementing the above command, we will get the following output, which shows all the key values of the Movie_attr column available in the Movie table:
OR
If we want that the PostgreSQL retrieve the output as a set, we can use the skey() function.
In the following command, we are using the skey() function instead of akeys() to get the output as a set:
SELECT skeys (Movie_attr)
FROM Movie; Output
After executing the above command, we will get the following output as a set:
Modify hstore data to sets
We can use the EACH() function to modify the hstore data to sets as we can see in the following command:
SELECT Movie_name,
(EACH(Movie_attr)).*
FROM Movie; Output
We will get the following output after successfully executing the above command, which displays the modified hstore data into sets:
Modifying the hstore data to JSON
To change the hstore data to JSON, PostgreSQL offers the hstore_to_json() function.
In the below command, we are using the hstore_to_json() function for converting the hstore data value into json:
SELECT Movie_name,
hstore_to_json (Movie_attr) json
FROM Movie; Output
We will get the following result after executing the above command, which displays that we have successfully modified the hstore data to the json:
Add key-value pairs to current rows
We can add a new key-value pair to current rows with the hstore column.
For example, we can add the Country key to the Movie_attr column of the Movie table as we can see in the following command:
UPDATE Movie
SET Movie_attrMovie_attr = Movie_attr || '"Country"=>"United States"':: hstore; Output
After executing the above command, we will get the below result, which shows that the three columns have been updated successfully.
Now, if we want to see that the “Country”=>”United States” value has been updated successfully.
SELECT Movie_name,
Movie_attr -> 'Country' AS Country
FROM Movie; Output
We will get the following result on implementing the above command, which displays that the Country Movie_attr value United States has been updated for the specified values.
Update current key-value pair
We can update the current key-value pair with the help of the UPDATE command.
For example: In the below command, we will update the value of the “Country” key to “USA”.
UPDATE Movie
SET Movie_attrMovie_attr = Movie_attr || '"Country"=> "USA"' ::hstore; Output
After implementing the above command, we will get the below message window, which displays that the values have been updated successfully.
Deleting the current key-value pair
PostgreSQL provides us with deleting the current key-value pair from an hstore column.
Let us see one sample example for our better understanding:
In the below command, we are deleting the “Country”=>”USA” key-value pair in the Movie_ attr column.
UPDATE Movie
SET Movie_attr = delete(Movie_attr, 'Country');Output
After implementing the above command, we will get the below message window, which says that specified values have been updated successfully.
Check for an explicit key in the hstore column
The ? operator is used to check for an explicit key in an hstore column within the WHERE clause.
In the following example, the below command retrieves all rows with Movie_attr, which contains key Movie_genres.
SELECT Movie_name,
Movie_attr->'movie_genres' as genres,
Movie_attr
FROM Movie
WHERE Movie_attr ? 'movie_genres'; Output
We will get the following result on implementing the above command, which shows the specified key in the hstore column.
Check for a key-value pair
We can use the @> operator to SELECT the hstore key-value pair.
Let us see one example to see the usage of the @> operator:
The below command is used to return all the rows containing a key-value pair from the Movie_attr column, and also matches “Language”=>”English”.
SELECT Movie_name
FROM Movie
WHERE Movie_attr @> '"language"=>"English"' :: hstore; Output
After successfully executing the above command, we will get the below result, representing the hstore key-value pair.
Selecting rows which having various specified keys
The ?& operator is used to select the rows whose hstore column having various keys.
In the following example, we can get Movie where Movie_attr column contains both running_time and rating keys:
SELECT Movie_name
FROM Movie
WHERE
Movie_attr ?& ARRAY [ 'running_time', 'rating' ]; Output
After executing the above command, we will get the below output:
Note: We can also use ?| operator in the ?& operator’s place to check if a row whose hstore column contains any key from a list of keys.
In the following command, we are using ?| operator instead of ?& to get those rows whose hstore column contains any key from a list of keys:
SELECT Movie_name
FROM Movie
WHERE
Movie_attr ?| ARRAY [ 'running_time', 'rating' ]; Output
After implementing the above command, we will get a similar result as compared to the output of ?& operator:
Overview
In the PostgreSQL hstore data type section to the most useful operations, we can perform against the hstore data type.
We have learned the following topics:
- The PostgreSQL hstore data type is used to store the key-value pair in the unit value.
- We enabled the PostgreSQL hstore using the Create Extension
- With the help of the -> operator, we can select the data for a specific value.
- We have used the hstore data type within the WHERE
- We can also retrieve the values from the hstore column using the avals() function.
- And to get the output as a set, we can use the svals() function.
- We can also return all the keys from the hstore value with the help of akeys()
- And to return the output as a set, we can use the skeys() function.
- We have used the EACH() function for modifying the hstore data into sets.
- In PostgreSQL, the hstore data type section, we can also change the hstore data to json using hstore_to_json() function.
- We can also add, update, delete the key-values pairs in the PostgreSQL hstore data type.
- We have also discussed the following operators, such as, ? (to check for an explicit key in an hstore column), @>(to check the key-value pair), ?&, and ?|( to select the rows whose hstore column having various keys).
Leave a Reply