SQL : Create a full record from 2 tables

Question

我有一个数据库结构(为了理解问题而简化到最大):

Table "entry" ("id" integer primary key)
Table "fields" ("name" varchar primary key, and others)
Table "entry_fields" ("entryid" integer primary key, "name" varchar primary key, "value")

对于给定的“entry.id”，我想获得该条目的详细信息，即。在单个 SQL 查询中链接到此条目的所有“entry_fields”。

一个例子可能会更好:

“字段”:

"result"
"output"
"code"
"command"

“条目”包含:

id : 842
id : 850

“entry_fields”包含:

entryid : 842, name : "result", value : "ok"
entryid : 842, name : "output", value : "this is an output"
entryid : 842, name : "code", value : "42"
entryid : 850, name : "result", value : "ko"
entryid : 850, name : "command", value : "print ko"

想要的输出是:

| id  | command    | output               | code | result |
| 842 | NULL       | "this is an output"  | 42   | ok     |
| 850 | "print ko" | NULL                 | NULL | ko     |

目的是能够添加一个“字段”而不改变任何“入口”表结构

我试过类似的东西:

SELECT e.*, (SELECT name FROM fields) FROM entry AS e

但是 Postgres 提示:

ERROR: more than one row returned by a subquery used as an expression

希望有人能帮助我!

Answer 1

按要求解决

虽然坚持这种不幸的设计，但最快的查询是使用 crosstab()，由附加模块 tablefunc 提供。此相关答案中的详细信息:

• PostgreSQL Crosstab Query

对于提出的问题:

SELECT * FROM crosstab(
      $$SELECT e.id, ef.name, ef.value
       FROM   entry             e
       LEFT   JOIN entry_fields ef
              ON ef.entryid = e.id
             AND ef.name = ANY ('{result,output,code,command}'::text[])
       ORDER  BY 1, 2$$

     ,$$SELECT unnest('{result,output,code,command}'::text[])$$
   ) AS ct (id int, result text, output text, code text, command text);

数据库设计

如果您没有大量的不同字段，将所有三个表合并到一个简单的表中会更简单和更有效:

CREATE TABLE entry (
   entry_id serial PRIMARY KEY
  ,field1   text
  ,field2   text
  , ... more fields
);

没有值的字段可以是NULL。 NULL 存储非常便宜(在 NULL 位图中基本上每列 1 位):

• How much disk-space is needed to store a NULL value using postgresql DB?
• Do nullable columns occupy additional space in PostgreSQL?

即使您有数百个不同的列，并且每个条目只填充很少的列，这仍然会占用更少的磁盘空间。

您的查询变得微不足道:

SELECT entry_id, result, output, code, command
FROM   enty;

如果你有太多的列¹，这不仅仅是一个错误的设计(通常，这可以折叠成更少的列)，考虑数据类型 hstore或 json/jsonb (在 Postgres 9.4 中)为 EAV存储。

¹ Per Postgres "About" page :

Maximum Columns per Table   250 - 1600 depending on column types

考虑这个相关的答案和替代方案:

• Use case for hstore against multiple columns

关于 dba.SE 上 EAV 结构的典型用例/问题的问题:

• Is there a name for this database structure?