Selecting table of properties as columns

Question

I have two tables, things and properties:

CREATE TABLE things (
  id SERIAL PRIMARY KEY
);

CREATE TABLE properties (
  thing_id INT,
  key TEXT,
  value TEXT
);

I want to select from things and join rows from properties as columns. For example, say I have the following:

INSERT INTO things DEFAULT_VALUES;  -- suppose id is 1
INSERT INTO properties (thing_id, key, value) VALUES
  (1, 'height', '5'),
  (1, 'width', '6'),
  (1, 'length', '7');

How can I select from things with height, width, and length as columns?

Also, I don't want to specifically select height, width, and length, but any rows that may be inside properties.

Do you know what columns you want in the final result set? If not, you need to do this using dynamic SQL. — Gordon Linoff, Jul 25 '14 at 00:08
A complete solution is not easy. For some cases a simpler solution is good enough. [Check one recent answer of mine](http://stackoverflow.com/a/24813587/131874). If it interest you I can write it for your case. — Clodoaldo Neto, Jul 25 '14 at 00:22
Ah, welcome to EAV design. Hopefully you sanitize inputs to `key` and `value`, or you'll get stuff like `key = 'Height'` (note capitalization), `key = 'hight'` (spelling), or `key = ' height'` (whitespace), and stuff like `key = 'height', value = 'five'`. 90+% of the time you can group/pivot rows into related tables (here, perhaps `Bounding_Box_Dimensions` or something) - most systems simply don't need the flexibility (because these are generally a pain to query/update safely). — Clockwork-Muse, Jul 25 '14 at 01:55

score 2 · Accepted Answer · edited May 23 '17 at 10:28

2

For just three columns:

SELECT t.thing_id
      ,max(CASE WHEN p.key = 'height' THEN p.value END) AS height
      ,max(CASE WHEN p.key = 'width'  THEN p.value END) AS width
      ,max(CASE WHEN p.key = 'length' THEN p.value END) AS length
FROM   things t
LEFT   JOIN properties p ON p.thing_id = t.id
WHERE  t.id = 1
GROUP  BY 1;

Or use crosstab() from the additional module tablefunc which is typically faster, and shorter for long lists of attributes:

SELECT * FROM crosstab(
      'SELECT t.thing_id, p.key, p.value
       FROM   things t
       LEFT   JOIN properties p ON p.thing_id = t.id
       WHERE  t.id = 1
       ORDER  BY 1'

      ,$$VALUES ('height'::text), ('width'), ('length')$$) -- add more
AS ct (thing_id int, height int, width int, length int);   -- add more

Types have to match. Detailed explanation:

PostgreSQL Crosstab Query

A completely dynamic list of columns cannot be achieved in a single query. I have tried many times. Here is what can be done:

Dynamic alternative to pivot with CASE and GROUP BY

edited May 23 '17 at 10:28

Community

1
1

answered Jul 25 '14 at 00:18

Erwin Brandstetter

605,456
145
1,078
1,228

1

Did you mean to left join table THINGS with PROPERTIES (not properties w/ propertie)? Also he said that those are not the only 3 columns he wants. – Brian DeMilia Jul 25 '14 at 00:20
Thanks for the answer, and for the meta-answer that a completely dynamic list of columns is impossible. For those curious, I ended up using an [`hstore`](http://www.postgresql.org/docs/9.0/static/hstore.html) instead. – Jul 26 '14 at 23:01
Two things you might be interested in: [`jsonb` in the upcoming Postgres 9.4](http://stackoverflow.com/questions/24292575/how-to-query-a-json-column-for-empty-objects/24296054#24296054) and [this related question on dba.SE about EAV with detailed answers](http://dba.stackexchange.com/questions/20759/is-there-a-name-for-this-database-structure). – Erwin Brandstetter Jul 27 '14 at 00:58

score 0 · Answer 2 · answered Jul 25 '14 at 00:11

May be you can try table aliases here:

SELECT  p1.key, p2.key, p3.key
  FROM properties as t1
  JOIN properties AS p1 ON p1.thing_id= t1.thing_id
  JOIN properties AS p2 ON p2.thing_id= t1.thing_id
  JOIN properties AS p3 ON p3.thing_id= t1.thing_id
 WHERE t1.thing_id = 1;

Selecting table of properties as columns

2 Answers2