How do I pick out one hstore column from a group in Postgres?

Question

Suppose I have a table foo in Postgres 11.5:

CREATE TABLE foo (an_int integer, a_varchar varchar(50));

I can select columns unique by integer and grab a couple of the a_varchar values as samples as follows:

SELECT an_int,
       min(a_varchar) min_a_varchar,
       max(a_varchar) max_a_varchar
FROM foo
GROUP BY 1;

Suppose I have a table foo2 instead:

CREATE TABLE foo2 (an_int integer, an_hstore hstore);

Is there a similar way to select one row per an_intand pluck out one or more hstore values? I tried using hstore_to_json() and it didn't work. Converting to varchar did work, but I don't know if that preserves data well.

So my best answer so far:

SELECT an_int,
       min(an_hstore::varchar) min_an_hstore,
       max(an_hstore::varchar) max_an_hstore
FROM foo2
GROUP BY 1;

`select one row per an_int` - ***any*** row? I doubt you need the "minimum hstore", that would be a very exotic requirement. — Erwin Brandstetter, Sep 25 '19 at 23:23

score 2 · Answer 1 · answered Sep 25 '19 at 19:56

It is easy to define min and max for hstore.

Here is an example for min:

CREATE FUNCTION hstore_smaller(hstore, hstore) RETURNS hstore
   LANGUAGE sql IMMUTABLE AS
'SELECT CASE WHEN $2 IS NULL OR $1 #<# $2 THEN $1 ELSE $2 END';

CREATE AGGREGATE min(hstore) (
   STYPE = hstore,
   SFUNC = hstore_smaller
);

Then you don't have to use any casts.

score 0 · Answer 2 · answered Sep 25 '19 at 23:39

Seems like you just want to pick N rows arbitrarily for each distinct value of an_int. (Not "the minimum/maximum hstore".)

For 1 row per group:

SELECT DISTINCT ON (an_int) an_int, an_hstore
FROM  foo2;

For N rows:

SELECT *
FROM  (SELECT *, row_number() OVER (PARTITION BY an_int) AS rn FROM foo2) sub
WHERE  rn < 3;

If the pick shall be deterministic instead of arbitrary, define your criteria and add ORDER BY clauses accordingly. Can also be "the minimum or maximum hstore" as ORDER BY an_hstore works out of the box - internally using those undocumented hstore operators Laurenz uses to create an aggregate function in another answer, which is smart, but also slow.

Select first row in each GROUP BY group?

Performance optimization for many rows per group:

Optimize GROUP BY query to retrieve latest row per user

How do I pick out one hstore column from a group in Postgres?

2 Answers2