hstore is a data type for storing sets of key/value pairs in Postgres, similar to hashes in Ruby and Perl, associative arrays in PHP or dictionaries in Python.

learn more… | top users | synonyms

1
vote
0answers
31 views

PostgreSQL: pg_upgrade failing because hstore incompatible

I'm trying to migrate from PostgreSQL 9.4.5_2 -> 9.5.3 I'm trying to use pg_upgrade (from 9.5.3 distribution) and getting the following error: pg_dump: [archiver (db)] query failed: ERROR: ...
1
vote
0answers
36 views

Could auto_analyze cause inconsistent (and bad) query plans?

I have a simple table which stores metadata about users in an hstore column. This table has ~1.1M rows. Table "public.stitcher_participant" Column | Type ...
0
votes
1answer
228 views

Postgres hstore check if a key exists and does not match value

I have a hstore field in an audit table that stores all the fields that have changed as a result of an operation. On inserts, the key, updated_by in changed_fields is NULL and on system updates it is ...
0
votes
1answer
72 views

Creating index using dynamic SQL [closed]

I have following code for creating gist index CREATE INDEX web_stats_project_107_properties_crosspromo_gist_idx ON web_stats_project_107 USING gist (properties) WHERE properties @> '"...
4
votes
0answers
64 views

Use case for hstore

This is the first time (I think) I have the chance to use the hstore data type, but I would like to hear from more experienced folks if what I have in mind is actually a good idea. Now, there's this ...
1
vote
1answer
153 views

How can I update an hstore[] field?

I have table products, in which there is an hstore array field countries_of_origin. So a product that is assembled from multiple products might have multiple countries of origin: [ { "country" =&...
3
votes
3answers
438 views

Dynamically convert hstore keys into columns for an unknown set of keys

I have a database that stores a bunch of custom fields using hstore. In order to merge it into another database that doesn't support hstore, I'd like to split the keys into extra columns. Users can ...
1
vote
1answer
162 views

How to store thousands of properties related to a record in PostgreSQL?

I have a database of approximately 300K records. For each of theses records there are up to 1600 related properties. Each of these properties have a simple key=>value format. This data is very static ...
2
votes
1answer
74 views

What is the etymology of the hstore datatype in postgres?

Where did the datatype get its name? Hash store?
1
vote
1answer
754 views

hstore type does not exist with hstore installed postgresql

I recently had to copy over a databases structure from one database to another with the command pg_dump -c -S database_name > pg_dump_date.sql I then ran the dump file on the new database with ...
4
votes
2answers
453 views

Adding hstore entries to an uninitialized (NULL) column

I got bitten by this "feature" recently. If your hstore column is uninitialized and you start adding entries to it, they are all silently swallowed without error. Is this expected? create table ...
0
votes
1answer
336 views

Remove all values of a key in PostgreSQL hstore

How do I delete/remove the value of a postgresql hstore column? I've got a table named pages and a column named settings - I need to remove the read_only value (basically, set it to empty string or ...
1
vote
1answer
294 views

Removing a Key From HSTORE in Postgresql

At the moment I have an hstore that describes the properties of various items. When searching I need to remove values that are unique to a each item (such as serial number, vin, and so on). According ...
5
votes
1answer
588 views

HStore vs. multiple tables with inheritance - do I have a good use case for HStore?

My broader question here has to do with when it is appropriate to use HStore vs. multiple tables vs. one table for storing document-like objects. Now, for my localized example: I’m designing a ...
1
vote
0answers
213 views

PostgreSQL 9.3 slaves don't recognize hstore as a type

I am using the postgres built in replication with two slave nodes and a single master. On the master node (as well as the slaves) the hstore extension is installed. But whenever a query using hstore ...
1
vote
2answers
916 views

Extract all tags from hstore field

I need to extract (copy) all hstore tags by object id from a postgres 9.2+postgis 2.1 database into an external file for post-processing. Since the keys and values vary per record, and I don't know ...
0
votes
1answer
1k views

Attributes from an hstore-column as separate columns in a view?

Let's assume we have a table where it has two columns, one an ID and one an hstore column, which stores key/value pairs. You could query that column as such: SELECT name, attributes->'device' as ...
2
votes
1answer
520 views

Strange Postgres Hstore syntax with curly braces {key=value}

I'm using the following program https://sourceforge.net/projects/jailer/ to extract a subset of data from my database with correct associations following my foreign key constraints. The program ...
0
votes
1answer
244 views

Optimizing PostgreSQL hstore for lots of small updates

I have a situation where I will have one web session per row, but this session will generate dozens of updates to an hstore field in that row, one k/v at a time. At the end, I will have a completed ...
2
votes
1answer
3k views

Postgres nested hstore select query

I have a carts table with items hstore column. An example entry in this column is: carts.items row#1 = { "1614" => {:quantity=>"100", :price_cents=>1655}, ...
3
votes
0answers
3k views

Achieve PostgreSQL hstore functionality in MySQL

Please suggest any MySQL 5.5+ Engine that can support collection of key-value pairs be stored in any column of a table. I don't know if this feature is available out of the box in MySQL like ...