Search code examples
postgresqlentity-attribute-valuehstore

Key value pair in PostgreSQL

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.

Solution

  • 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.