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
1
vote
0answers
63 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
2answers
70 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
42 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
1answer
353 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
306 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
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. ...
3
votes
1answer
123 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 ...
1
vote
1answer
352 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 ...
2
votes
1answer
374 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?
0
votes
2answers
473 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 ...
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: ...
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 ...
3
votes
1answer
5k 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
516 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
1answer
249 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
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" =&...
6
votes
3answers
4k 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
532 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 ...
4
votes
1answer
130 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
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 ...
0
votes
1answer
809 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
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 ...
5
votes
1answer
782 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
278 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 ...
2
votes
2answers
2k 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
2k 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
821 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
340 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
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}, ...
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 ...