postgresql - Is it possible to name SQL result columns from rows in another table? (Postgres)

Question

Basically I have users with a dynamic attributes table. Simplified:

SELECT * FROM users;
 id  |                  email                   
-----+------------------------------------------
  1 | [email protected]


SELECT * FROM user_attributes;
 id |      name      
----+----------------
  1 | Salutation
  2 | Given Name
  3 | Surname
  4 | Alias
  5 | Address
  6 | Address 2
  7 | Address 3
  8 | City
  9 | Region
....

SELECT * FROM user_attribute_values;
 client_id | attribute_id | value 
-----------+--------------+-------

What I'm looking to do is a SELECT that would return columns user_id, city, region where city & region are not empty.

The reason for the user_attributes table is one may want to store any number of custom fields about the user, and it's impossible to know beforehand what they will be to create them as columns of the user table.

Answer 1

This is based on a principal misunderstanding of the inner workings of Postgres and EAV designs.

If you don't have hundreds of different fields or a dynamic set of attribute types, use a single table with all columns - except for database normalization. Columns without value are filled with NULL.
Null storage is very cheap, occupying 1 bit per column in the table for the null bitmap, typically allocated in units of 8 bytes to cover 64 columns. See:

• How much disk-space is needed to store a NULL value using postgresql DB?

A separate row for a single additional attribute occupies at least an additional 36 bytes.

4  bytes item identifier
23 bytes heap tuple header
1  byte  padding
8  bytes minimum row data size

Typically more, due to padding and additional overhead.

There would have to be hundreds of different, sparsely populated columns before such an unwieldy EAV design could pay - and hstore or jsonb in Postgres 9.4 would be superior solutions for that. There is hardly any room in between for your design, and if there was, you'd probably be using an enum for the type.

At the same time, queries are more complicated and expensive. We're in a tight spot here.

Instead use a table layout like this:

CREATE TABLE users (
   users_id serial PRIMARY KEY
 , salutation text
 , given_name text
 , surname text
 , alias text
 ... (many) more columns
);

CREATE TABLE address (
   address_id serial PRIMARY KEY
 , users_id int REFERENCES users
 , city text  -- or separate TABLE city incl region_id etc. ...
 , region_id int REFERENCES region
 , address  text
 ... (many) more columns
);

Closely related answer with more advice:

• SQL : Create a full record from 2 tables