The Stack Overflow podcast is back! Listen to an interview with our new CEO.

Questions tagged [hstore]

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.

Filter by
Sorted by
Tagged with
7
votes
3answers
5k 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 ...
5
votes
1answer
6k 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 ...
5
votes
1answer
788 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 ...
4
votes
2answers
2k 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 ...
4
votes
1answer
137 views

What is the etymology of the hstore datatype in postgres?

Where did the datatype get its name? Hash store?
4
votes
1answer
4k 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
1answer
268 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 ...
4
votes
0answers
5k 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 ...
3
votes
1answer
3k 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 ...
3
votes
1answer
139 views

Adding id column of table to a hstore column postgres [closed]

I am trying to figure out how to store the id column of a table into a hstore column. That way the id is stored in two places. Going forward the table will be moved to another database and the ...
3
votes
1answer
3k 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: ...
2
votes
2answers
3k 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 ...
2
votes
1answer
411 views

Can I alter an HSTORE column to JSON datatype?

Is there a way to modify an existing column of HSTORE datatype (that has data) to JSON datatype in a Postgres database?
2
votes
1answer
4k 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}, ...
2
votes
1answer
850 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 ...
1
vote
1answer
1k 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 ...
1
vote
2answers
111 views

PostgreSQL: hstore trgm search on fixed key

I have a table of products with custom properties for each product. So I have props::hstore column in my table. I want the user to be able to search products by key/value by first selecting the key ...
1
vote
1answer
1k views

updating a hstore with multiple new columns

I am trying to add 2 new hstore key/value pairs in my postgreSQL database. Is it possible to do this in one statement or do I need to break it up? Here is what I have so far and it is not working. ...
1
vote
1answer
426 views

Create hstore from key-value result set

I have following columns: key | value ----------- foo | 1 bar | 2 Is there a recommended way to turn this into hstore? {foo => 1, bar => 2}
1
vote
1answer
409 views

How to query hstore tag keys that match part of the filter string

In PostgreSQL query, how can I filter the results by using any record that tags with a key like %building% something like: .. where Table.tags ? '%building%' I didn't find an answer within ...
1
vote
2answers
46 views

How to filter hstore where values are not null in a trigger?

The easiest way to explain the question is in the usage of an audit trigger. However, on insert it saves all values, including null values. I want to filter out the null values in the hstore(NEW.*). ...
1
vote
1answer
332 views

Create extension hstore

I want to use hstore in my postgresql server running on CentOS 7 but hstore.control is not in my postgresql extension folder. I searched for that and came up with the solution to install postgresql93-...
1
vote
1answer
5k 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" =&...
1
vote
1answer
570 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 ...
1
vote
0answers
134 views

Do hstore and jsonb have the same plumbing internally?

Is hstore internally implemented identically as jsonb, just with a constraint that the data must be flat? If not, when/why should hstore be used instead of jsonb?
1
vote
1answer
61 views

Why doesn't jsonb constructor from a NULL record behave like hstore constructor?

When I construct an hstore CREATE EXTENSION hstore; SELECT hstore(null::my_type); hstore ------------------------------------------------ "name"=>NULL, "...
1
vote
0answers
50 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 ...
1
vote
0answers
287 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 ...
0
votes
1answer
346 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 ...
0
votes
2answers
508 views

removing specific Key and value from Hstore in postgres

i have a data which is in Hstore form so i want to select a specific key and value from data and then delete them, as i use this query it is find all rows which this key and value exist on it but i ...
0
votes
1answer
546 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 @> '"...
0
votes
1answer
849 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 ...