CODEWITHSUNDEEP
postgresql
Manuel G
Manuel G

Reputation: 1279

Postgresql Select all columns and column names with a specific value for a row

I have a table with many(+1000) columns and rows(~1M). The columns have either the value 1 , or are NULL.

I want to be able to select, for a specific row (user) retrieve the column names that have a value of 1.

Since there are many columns on the table, specifying the columns would yield a extremely long query.

Upvotes: 0

Views: 1912

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 325141

You're doing something SQL is quite bad at - dynamic access to columns, or treating a row as a set. It'd be nice if this were easier, but it doesn't work well with SQL's typed nature and the concept of a relation. Working with your data set in its current form is going to be frustrating; consider storing an array, json, or hstore of values instead.

Actually, for this particular data model, you could probably use a bitfield. See bit(n) and bit varying(n).

It's still possible to make a working query with your current model PostgreSQL extensions though.

Given sample:

CREATE TABLE blah (id serial primary key, a integer, b integer, c integer);
INSERT INTO blah(a,b,c) VALUES (NULL, NULL, 1), (1, NULL, 1), (NULL, NULL, NULL), (1, 1, 1);

I would unpivot each row into a key/value set using hstore (or in newer PostgreSQL versions, the json functions). SQL its self provides no way to dynamically access columns, so we have to use an extension. So:

SELECT id, hs FROM blah, LATERAL hstore(blah) hs;

then extract the hstores to sets:

SELECT id, k, v FROM blah, LATERAL each(hstore(blah)) kv(k,v);

... at which point your can filter for values matching the criteria. Note that all columns have been converted to text, so you may want to cast it back:

SELECT id, k FROM blah, LATERAL each(hstore(blah)) kv(k,v) WHERE v::integer = 1;

You also need to exclude id from matching, so:

regress=> SELECT id, k FROM blah, LATERAL each(hstore(blah)) kv(k,v) WHERE v::integer = 1 AND 
k <> 'id';
 id | k 
----+---
  1 | c
  2 | a
  2 | c
  4 | a
  4 | b
  4 | c
(6 rows)

Upvotes: 1

Related Questions