1

I need to save key value pairs in PostgreSQL database which will have some basic info regarding the record.

After some googling on the topic I found out hstore is one of the options. But even after going through the documentation I am not able to figure out how to add records in a table with a hstore column and also how they are returned in the result and how I can parse it.

I am totally new to PostgreSQL so any code reference will be great.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Ashu
  • 1,703
  • 4
  • 16
  • 23

1 Answers1

6

To use the hstore data type, you need to install the additional module hstore first. Once per database:

CREATE EXTENSION hstore;

CREATE EXTENSION has been added with Postgres 9.1. Older versions have other methods.

Read more about the pros and (many) cons of EAV (Entity-Attribute-Value) storage in an RDBMS under this related question on dba.SE.

In Postgres 9.2+ consider json as alternative.
In Postgres 9.4+ consider jsonb.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228