For an API I am building I am supposed to write a function with a single hstore argument containing colname => value pairs so that queries may be filtered with a WHERE clause including an indefinite amount of subclauses. I currently have the following function definition (not a MWE):

CREATE OR REPLACE FUNCTION api.func( conds HSTORE )
RETURNS TABLE (LIKE api.tab) AS 
$$
BEGIN
	RETURN QUERY
		SELECT * 
		FROM api.tab 
		WHERE -- conds here
        LIMIT 25 ;
END;
$$ LANGUAGE plpgsql;

I am not really sure how to continue. Could someone please give me a hint?

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 PostgreSQL equivalent to **HSTORE** which is immensely useful and adds some of NoSQL benefits .. Example in PostgreSQL - SELECT 'a=>1,a=>2'::hstore; hstore ---------- "a"=>"1" source - http://www.postgresql.org/docs/current/static/hstore.html

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 psql 'hstore_key'::TEXT = 'hstore_value' I get the error that operator does not exist: schema_name.hstore -> text I get a similar error if I do not try and cast the key operator does not exist: schema_name.hstore -> unkown My first instinct was to simply drop the hstore extension and re-install it, but doing so would also drop my numerous user functions, triggers, and other dependencies on the hstore type. Short of doing a cascading drop of the hstore exentsion, is there a way that I can fix this error? I am using a postgresql 9.3 server.

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 add new custom fields and so I can't rely on knowledge of the keys ahead of time. Which makes the answer at "Attributes from an hstore-column as separate columns in a view?" not applicable to my problem. Where a record doesn't have a key present in other records, it should get the same column with a **null** value. How do I do this?

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}, "1938" => {:quantity=>"50", :price_cents=>1955}, "1983" => {:quantity=>"100", :price_cents=>2255}, "1322" => {:quantity=>"10", :price_cents=>4455}, "1691" => {:quantity=>"25", :price_cents=>1055}, "1734" => {:quantity=>"20", :price_cents=>1255} } carts.items row#2 = {"1614"=>{:quantity=>"50", :price_cents=>1655}} So my carts table would look like this: id | items ---------+------- 1 | {"1614"=>{:quantity=>"100", :price_cents=>1655}, "1938" => {:quantity=>"50", :price_cents=>1955},"1983"=>{:quantity=>"100", :price_cents=>2255},"1322"=>{:quantity=>"10", :price_cents=>4455},"1691"=>{:quantity=>"25", :price_cents=>1055},"1734"=>{:quantity=>"20", :price_cents=>1255}} 2 | {"1614"=>{:quantity=>"50", :price_cents=>1655}} You will notice that there is one duplicate id (1614) in the hash, but its quantity is different. I want to write a query that will return a table with the item id counts and the total quantity. It should look like this: item_id | count | total ---------+-------+------ 1614 | 2 | 150 1938 | 1 | 50 1983 | 1 | 50 1322 | 1 | 100 Here is the query that I am working with: SELECT skeys(carts.items) as item_ids, COUNT(*) AS count, svals(carts.items) as items FROM carts GROUP BY skeys(carts.items), svals(carts.items) It returns: item_id | count | total ---------+-------+------ 1614 | 1 | {:quantity=>100} 1614 | 1 | {:quantity=>50} 1938 | 1 | {:quantity=>50} 1983 | 1 | {:quantity=>50} 1322 | 1 | {:quantity=>100} I aslo have tried: SELECT key, count(*) FROM (SELECT (each(items)).key FROM carts) AS stat GROUP BY key ORDER BY count DESC, key; Which gives me this: item_id | count ---------+------- 1614 | 2 1938 | 1 1983 | 1 1322 | 1

Where did the datatype get its name? Hash store?

I'm tasked with scrapping a big website with a decade-long history and for the purpose of corpus & prompt engineering it into multiple datasets of various value; in some cases, this value is not immediately apparent. We don't really know the qualities of most fundamental distributions in advance, so this job would require a whole lot of aggregating, plotting, regressing, and sampling. Lots of preliminary work before we could ever get started on actually working the prompts. Good news we have Postgres (Timescale!) and Grafana to work it out. For reference: there are 743.000 forum posts and 35.5 million comments to go with them, the good two thirds of these comments have been rated, 23.1 million total. The normal post and & forum would have anywhere from 5–50 votes to them, all ids are integer, and all votes are +1/–1. I would have to scrap, and SQL COPY them in relatively quickly but most important of all is that I'm also able to have this data readily available part of some large window-aggregating, percentile-calculating materialisation for different statistics and that kind of stuff. There are primarily three types of queries we're going to be doing re: *votes* are concerned: 1. Selecting set of up/down votes for individual posts quickly; 2. Reverse-Joining on all up/down votes per individual user; 3. Ranking on voting net-positive, negative user clusters, re: PageRank algebra; So the question is whether we want vote-normal, or post-normal representation.

CREATE TABLE ballot
(
    post_id    int,
    comment_id int,
    user_id    int  not null,
    vote       bool not null
);
CREATE INDEX ON ballot USING brin (post_id, comment_id);
CREATE INDEX ON ballot USING btree (user_id, post_id);
CREATE INDEX ON ballot USING btree (user_id, comment_id);

Correct me if I'm wrong but I think I can leverage BRIN for (1) just because I know ahead of time I'll be copying votes in a particular order, i.e. votes for one comment would go in one uninterrupted block. If this is not the case, thought I might do simply a primary key on (post_id, comment_id, user_id) which would yield a three-way composite btree which is something that you would expect. I thought perhaps given the set of requirements and given that the ingest order is known ahead of time, a brin + two leaner btrees would work better. Maybe I would have to find out but first— I have to make a decision how it will be structured during ingest. The other option here involves some denormalised representation and a reverse index. When initially considering array v. hstore v. jsonb, my intuition suggested given the set-like nature of my data that hstore would work best, and my intuition was somewhat reassured when I stumbled upon a only tangentially relevant [benchmark](https://gist.github.com/semaperepelitsa/66527f35f5127ed8dbb95974e68139b7) showing nonmarginal gains for hstore in set-like operations but the truth is for my use case that is the ? operator, it probably wouldn't matter all too much. As long as the denormalised form helps with locality and leads to a smaller index that I could always fit in memory... it doesn't matter what particular inverse index is going to do the job. I will do my example in hstore:

CREATE TABLE ballot
(
    post_id    int,
    comment_id int,
    -- hstore(user_id, null) or hstore(-user_id, null)
    votes      hstore not null 
);
CREATE UNIQUE INDEX ON ballot USING btree (post_id);
CREATE UNIQUE INDEX ON ballot USING btree (comment_id);
CREATE INDEX ON ballot USING gin (votes);

The likes are encoded as positive and dislikes— as negative ids. The idea here is now that our schema is subject-normal we can rely on two normal btrees for random-access and have a reverse index do the heavy lifting using ? operator which is performed using by hstore. The whole table is supposed to get anywhere from 5-50 times smaller, and hopefully that would make a difference! I've also considered other variants, like using two separate hstore columns for pro's and con's, or to keep the user_id constant regardless of vote, and encode it as the value instead, so I did a simple query from a user table that has been ingested already to see how the planner would react:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
    id,
    login,
    a.post_id as post_up,
    a.comment_id as comment_up,
    b.post_id as post_down,
    b.comment_id as comment_down
FROM users u
LEFT JOIN ballot a ON a.pros ? id::text
LEFT JOIN ballot b ON b.cons ? id::text;

QUERY PLAN
Nested Loop Left Join  (cost=0.07..1359142.33 rows=3247298 width=28) (actual time=0.033..102.884 rows=49494 loops=1)
"  Output: u.id, u.login, a.post_id, a.comment_id, b.post_id, b.comment_id"
  Buffers: shared hit=99373
  ->  Nested Loop Left Join  (cost=0.00..802702.87 rows=400901 width=20) (actual time=0.019..17.434 rows=49494 loops=1)
"        Output: u.id, u.login, a.post_id, a.comment_id"
        Join Filter: (a.pros ? (u.id)::text)
        Buffers: shared hit=385
        ->  Seq Scan on public.users u  (cost=0.00..879.94 rows=49494 width=12) (actual time=0.012..5.382 rows=49494 loops=1)
"              Output: u.id, u.login, u.profile"
              Buffers: shared hit=385
        ->  Materialize  (cost=0.00..22.15 rows=810 width=40) (actual time=0.000..0.000 rows=0 loops=49494)
"              Output: a.post_id, a.comment_id, a.pros"
              ->  Seq Scan on public.ballot a  (cost=0.00..18.10 rows=810 width=40) (actual time=0.002..0.002 rows=0 loops=1)
"                    Output: a.post_id, a.comment_id, a.pros"
  ->  Bitmap Heap Scan on public.ballot b  (cost=0.07..1.31 rows=8 width=40) (actual time=0.001..0.001 rows=0 loops=49494)
"        Output: b.post_id, b.comment_id, b.pros, b.cons"
        Recheck Cond: (b.cons ? (u.id)::text)
        Buffers: shared hit=98988
        ->  Bitmap Index Scan on ballot_down_idx  (cost=0.00..0.07 rows=8 width=0) (actual time=0.001..0.001 rows=0 loops=49494)
              Index Cond: (b.cons ? (u.id)::text)
              Buffers: shared hit=98988
Query Identifier: 3422089729452766994
Planning:
  Buffers: shared hit=8
Planning Time: 0.265 ms
Execution Time: 105.154 ms

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
    id,
    login,
    a.post_id as post_up,
    a.comment_id as comment_up,
    b.post_id as post_down,
    b.comment_id as comment_down
FROM users u
-- notice that both joins are now on the same column
LEFT JOIN ballot a ON a.pros ? id::text
LEFT JOIN ballot b ON b.pros ? ('-'||id::text);

QUERY PLAN
Nested Loop Left Join  (cost=0.00..7397544.54 rows=3247298 width=28) (actual time=0.023..21.634 rows=49494 loops=1)
"  Output: u.id, u.login, a.post_id, a.comment_id, b.post_id, b.comment_id"
  Join Filter: (a.pros ? (u.id)::text)
  Buffers: shared hit=385
  ->  Nested Loop Left Join  (cost=0.00..902928.22 rows=400901 width=20) (actual time=0.019..13.729 rows=49494 loops=1)
"        Output: u.id, u.login, b.post_id, b.comment_id"
        Join Filter: (b.pros ? ('-'::text || (u.id)::text))
        Buffers: shared hit=385
        ->  Seq Scan on public.users u  (cost=0.00..879.94 rows=49494 width=12) (actual time=0.013..4.383 rows=49494 loops=1)
"              Output: u.id, u.login, u.profile"
              Buffers: shared hit=385
        ->  Materialize  (cost=0.00..22.15 rows=810 width=40) (actual time=0.000..0.000 rows=0 loops=49494)
"              Output: b.post_id, b.comment_id, b.pros"
              ->  Seq Scan on public.ballot b  (cost=0.00..18.10 rows=810 width=40) (actual time=0.002..0.002 rows=0 loops=1)
"                    Output: b.post_id, b.comment_id, b.pros"
  ->  Materialize  (cost=0.00..22.15 rows=810 width=40) (actual time=0.000..0.000 rows=0 loops=49494)
"        Output: a.post_id, a.comment_id, a.pros"
        ->  Seq Scan on public.ballot a  (cost=0.00..18.10 rows=810 width=40) (actual time=0.002..0.002 rows=0 loops=1)
"              Output: a.post_id, a.comment_id, a.pros"
Query Identifier: 8470202287074307650
Planning:
  Buffers: shared hit=7
Planning Time: 0.231 ms
Execution Time: 23.143 ms

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
    id,
    login,
    a.post_id as post_up,
    a.comment_id as comment_up,
    b.post_id as post_down,
    b.comment_id as comment_down
FROM users u
-- notice that both joins are now on the same column
LEFT JOIN ballot a ON a.pros @> hstore(id::text, '1')
LEFT JOIN ballot b ON b.pros @> hstore(id::text, '-1');

QUERY PLAN
Nested Loop Left Join  (cost=0.00..8209369.07 rows=3247298 width=28) (actual time=0.024..21.786 rows=49494 loops=1)
"  Output: u.id, u.login, a.post_id, a.comment_id, b.post_id, b.comment_id"
"  Join Filter: (b.pros @> hstore((u.id)::text, '-1'::text))"
  Buffers: shared hit=385
  ->  Nested Loop Left Join  (cost=0.00..902928.22 rows=400901 width=20) (actual time=0.020..13.496 rows=49494 loops=1)
"        Output: u.id, u.login, a.post_id, a.comment_id"
"        Join Filter: (a.pros @> hstore((u.id)::text, '1'::text))"
        Buffers: shared hit=385
        ->  Seq Scan on public.users u  (cost=0.00..879.94 rows=49494 width=12) (actual time=0.014..4.348 rows=49494 loops=1)
"              Output: u.id, u.login, u.profile"
              Buffers: shared hit=385
        ->  Materialize  (cost=0.00..22.15 rows=810 width=40) (actual time=0.000..0.000 rows=0 loops=49494)
"              Output: a.post_id, a.comment_id, a.pros"
              ->  Seq Scan on public.ballot a  (cost=0.00..18.10 rows=810 width=40) (actual time=0.002..0.002 rows=0 loops=1)
"                    Output: a.post_id, a.comment_id, a.pros"
  ->  Materialize  (cost=0.00..22.15 rows=810 width=40) (actual time=0.000..0.000 rows=0 loops=49494)
"        Output: b.post_id, b.comment_id, b.pros"
        ->  Seq Scan on public.ballot b  (cost=0.00..18.10 rows=810 width=40) (actual time=0.002..0.002 rows=0 loops=1)
"              Output: b.post_id, b.comment_id, b.pros"
Query Identifier: -9035379806324338655
Planning:
  Buffers: shared hit=71
Planning Time: 0.389 ms
Execution Time: 23.297 ms

105ms / 23ms / 23ms In all honesty, I expected no.1 to do much better and no.2 to do much worse! However, neither 2 nor 3 seem to be using the index! Would love to get some insight on this from experienced DBA's. Best regards, Ilya

I have a table create table device_entity ( id uuid not null primary key, name varchar(255), metadata hstore ); This table has entries with 'country' = 'uk' in the metadata field. This query returns all entries where the key 'country' is present in the metadata field. SELECT * from device_entity where metadata ? 'country' How can I select all entries where the key 'country' = 'uk'?

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.*). What is the simplest / fastest way to do this? Source: https://github.com/2ndQuadrant/audit-trigger/blob/master/audit.sql#L134

audit_row.row_data = hstore(NEW.*) - excluded_cols;

This obviously doesn't work but explains, hopefully, what the answer will look like.

audit_row.row_data = hstore(hstore(NEW.*) FILTER (WHERE value IS NOT NULL)) - excluded_cols;

Preference will go to answer that doesn't use an user created function, e.g. use only Postgres functions / operators. This is for >= PostgreSQL 11. I am looking for a solution with hstore, not json or jsonb. Alternative ways with json or jsonb may be included, but hstore is preferred.

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 test_hstore(id int, map hstore); insert into test_hstore(id,map) values(0, ''); INSERT 0 1 select * from test_hstore ; id | map ----+----- 0 | update test_hstore set map = map || hstore('key1', 'value1') where id = 0; UPDATE 1 select * from test_hstore; id | map ----+------------------ 0 | "key1"=>"value1" update test_hstore set map = null where id = 0; UPDATE 1 select * from test_hstore; id | map ----+-------- 0 | (null) update test_hstore set map = map || hstore('key1', 'value1') where id = 0; UPDATE 1 select * from test_hstore; id | map ----+-------- 0 | (null) If I cannot have a not null constraint on the column, can I safeguard myself by doing something like that(this doesn't actually work): UPDATE test_hstore SET map = (IF map IS NULL THEN '' || hstore('key1', 'value1') ELSE map || hstore('key1', 'value1')) WHERE id = 0;

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 web app in which we import payroll data in the form of XML files, which look roughly like this simplified version: Smith John 9999.99 Each employee carries extremely detailed information, and when I say "several other hundreds of tags" it's because they will usually range between 800 and upwards of 1400. And this is for each month. Apart from a set of core tags, each employee can have a different combination of them, hence the very fluctuating but very realistic figures I gave above. Now, part of this data is imported with a long, slow and very complex process, and I've observed that, with increasing frequency, we are finding ourselves saying "gosh, if only we had always imported *that particular tag!*". While the import process is highly configurable, making it run for just a small set of data is slow, impractical and downright painful. Adding whatever customization is needed to import the hypothetical new tag *from now on* is much easier, but for building up historic data as if we always imported it, it's messy and error prone. As an added bonus, the task always falls on those two guys, and I'm one of them, and I would love to make our life a tad simpler. This is why I'm thinking of writing a quick tool that, during the night, will crack those XML files open and, for each month and each employee, create a record with an hstore column containing all of the employee's tags for that month. Being an absolute beginner at hstore, this looks to me like a very good use case for it, especially if we consider that: - since the tags can differ for each employee, this is essentially schema-less data. - storing the tags as an EAV with one row per tag would mean approximately 240k rows per month for a 200-employees company (well into the 2.8M per year). Nothing to be concerned about, sure, but there's not only one customer. And one of them has over 7,000 employees (which would be 100M records per year). - this data will only ever need to be read, **never** changed. Plus, it won't even be read that often anyway. - I don't really care or know what any given tag means. I just want to store it for future use, telling me which one is needed is the job of the domain experts. Again, schema-less. The table I would design would look a bit like this: - id bigserial - user_id - file_timestamp (it's embedded on the name of the file) - employee_id_1 varchar - employee_id_2 varchar - month date - file_id (id of the XML file, it gets logged in a table before being imported) - tags hstore (of employee_id 1 and 2, the former looks like the US SSN, the latter is assigned from our payroll app). I would also create a unique index on (user_id, employee_id_1, employee_id_2, month, file_id). Not 100% sure about the order of the columns, but I think it would accommodate most SELECTs where one wants to progressively narrow the data down. Also, I wouldn't want to duplicate the table for each customer, and I don't want or need it to be user-visible. I would create a dedicated schema and stick it in there, which would be a bit easier to manage in case I want to partition it at some point in the future. It's going to be a huge table (not so much for the number of rows, but for the space required for each of them), but keeping into its own schema also means it will be easier to exclude from most backups. Plus, we keep the original XML files anyway, so it wouldn't even be hard to rebuild if things go awry. With such a design, it looks like generating historical data with a bunch of ad-hoc one-off queries would be child's play. But since I'm not an expert, I was wondering if: 1. this is actually a good use case for hstore 2. there are obvious flaws in my design 3. there are things I should pay attention to in order to avoid painting myself into a corner when hstore enters the scene 4. since the tags are in a sizable number but still *not that many* per row, would it be worth it creating an index on the hstore keys?

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?

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 and then entering the value with autocomplete feature. So I need two steps here: 1. Get all the keys in props field. SELECT DISTINCT with skeys should work here, but I don't understand how to create index. 2. Find most relavent values for fixed key (autocomplete feature). But seems like gin_trgm_ops can create index only on value not on value for fixed keys. Also, maybe it can be a good idea to change the hstore with jsonb, but I don't see why it can be more perfomant.

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 the postgresql documentation (https://www.postgresql.org/docs/9.6/static/hstore.html)

When I construct an [hstore](https://www.postgresql.org/docs/current/static/hstore.html) CREATE EXTENSION hstore; SELECT hstore(null::my_type); hstore ------------------------------------------------ "name"=>NULL, "street"=>NULL, "location"=>NULL (1 row) While the [jsonb](https://www.postgresql.org/docs/current/static/functions-json.html) method, SELECT to_jsonb(null::my_type); to_jsonb ---------- (1 row) This creates a surprising effect too when you try to merge (||) another like type, SELECT hstore(null::my_type) || hstore('name', 'Evan'); ?column? -------------------------------------------------- "name"=>"Evan", "street"=>NULL, "location"=>NULL (1 row) SELECT to_jsonb(null::my_type) || jsonb_build_object('name', 'Evan'); ?column? ---------- (1 row)

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

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 and nearly all operation will be reads. Below are the options I'm thinking about to solve this problem. **1)** A column for each property This falls apart quickly as the max number of columns available in postgres is ~1600. So really, this isn't an option. **2)** Create an hstore or JSONB column to store the key value columns. This seems to work but I've read there are practical limits to how much data should be stored in hstore or JSONB. There are a lot of key/values and I think both hstore and JSONB will store these in TOAST which could have a negative impact on performance. What potential issues/practical limits should I be aware of if using approach #2? What are some other approaches to solving this problem?

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-contrib for postgresql version 9.3. But when I want to install the package, there is no package available for that. How can I solve this issue?

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. with foo as ( select id, new_value_1, new_value_2 from table 3 ) update public.table1 set hstore = hstore('new_key_1',foo.new_value_1), hstore = hstore('new_key_2',foo.new_value_2) where id = foo.id I am getting a error where I cannot call the same row in a update twice in a row. How would can i insert into the hstore two different values I am selecting in the foo statement above as values with a new key into a hstore of columns that already exist. I just need to tag the rows so even if the values change the original values are stored for future issue resolution.

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: incompatible library "/usr/local/lib/postgresql/hstore.so": version mismatch Anyone know how to fix this? must I pg_dumpall and then reload? That has worked in the past. Trying to use pg_upgrade to save some steps.