postgresql
  1. postgresql-hstore
PreviousNext

hstore - (PostgreSQL Data Types)

PostgreSQL supports a wide variety of data types, including the hstore data type. hstore is a built-in key-value data type that allows you to store sets of key/value pairs within a single PostgreSQL value. In this tutorial, we'll discuss the syntax, example, output, explanation, use, important points, and summary of the hstore data type in PostgreSQL.

Syntax

hstore ( [key => value [, ...]] )

Example

Let's create a table with an hstore column and insert some data.

CREATE TABLE my_table (
  id SERIAL PRIMARY KEY,
  data hstore
);

INSERT INTO my_table (data) VALUES
  ('"name" => "John", "age" => "30"'),
  ('"name" => "Sarah", "age" => "25"');

The data column of my_table is an hstore column that allows us to store key-value pairs within a single PostgreSQL value.

Output

When we select data from the my_table table, we get the following output:

 id |                 data                  
----+---------------------------------------
  1 | "name"=>"John", "age"=>"30"
  2 | "name"=>"Sarah", "age"=>"25"

Explanation

In this example, we used the hstore data type to create an hstore column in the my_table table. We then inserted two rows into the table, each with a set of key/value pairs in the data column.

Use

The hstore data type is useful for storing key-value pairs within a single PostgreSQL value. This is useful for situations where a simple key/value store is needed, but the data needs to be searchable within the database.

Important Points

  • Values in an hstore column are always stored as strings.
  • The key/value pairs in an hstore column can be accessed using the -> and ->> operators in PostgreSQL.
  • Only one key/value pair can exist in a single hstore value.
  • If a key/value pair is already present in an hstore value and a new key/value pair is added with the same key, the new value will overwrite the old value.

Summary

In this tutorial, we discussed the hstore data type in PostgreSQL. We covered the syntax, example, output, explanation, use, and important points of the hstore data type. With this knowledge, you can now use the hstore data type to store key/value pairs within a single PostgreSQL value in your PostgreSQL databases.

JSONArray
Published on: