¿Es posible nombrar columnas de resultados de SQL de filas en otra tabla? (Postgres)

Básicamente tengo usuarios con una tabla de attributes dynamics. Simplificado:

SELECT * FROM users; id | email -----+------------------------------------------ 1 | example@example.com 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 -----------+--------------+------- 

Lo que estoy buscando hacer es un SELECTO que devuelva las columnas user_id, city, region donde city & region no están vacías.

El motivo de la tabla user_attributes es que uno quiera almacenar cualquier cantidad de campos personalizados sobre el usuario, y es imposible saber de antemano qué serán para crearlos como columnas de la tabla de usuarios.

Use INNER JOIN para eso:

 SELECT u.id, a_city.value AS city, a_region.value AS region FROM users u INNER JOIN user_attribute_values a_city ON a_city.client_id = u.id AND a_city.attribute_id = 8 INNER JOIN user_attribute_values a_region ON a_region.client_id = u.id AND a_region.attribute_id = 9 WHERE LENGTH(a_city.value) > 0 AND LENGTH(a_region.value) > 0 

Esto se basa en un malentendido principal del funcionamiento interno de los layouts de Postgres y EAV .

Si no tiene cientos de campos diferentes o un set dynamic de types de attributes, use una sola tabla con todas las columnas , excepto para la normalización de la database . Las columnas sin valor se rellenan con NULL .
El almacenamiento nulo es muy barato .

  • 1 bit por columna en la tabla para el bitmap nulo, típicamente asignado en unidades de 8 bytes que cubre 64 columnas.
  • Una fila separada para un solo atributo adicional ocupa al less 28 bytes adicionales.

     4 bytes item pointer 23 bytes heap tuple header 1 byte padding 

    Típicamente más, debido al relleno y la sobrecarga adicional.

Tendría que haber cientos de columnas diferentes, escasamente pobladas, antes de que un layout tan hstore EAV pudiera pagar, y hstore o jsonb en Postgres 9.4 serían soluciones superiores para eso . Apenas hay espacio entre ellos para su layout, y si lo hubiera, probablemente enum una enum para el tipo.

Al mismo time, las consultas son más complicadas y costosas. Estamos en una situación difícil aquí.

En su lugar, use un layout de tabla como este:

 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 ); 

Respuesta estrechamente relacionada con más consejos:

  • SQL: crea un logging completo a partir de 2 tablas
 select client_id, min(case when attribute_id = 8 then value else '0' end) as city, min(case when attribute_id = 9 then value else '0' end) as region from user_attribute_values group by clientid having min(case when attribute_id = 8 then value else '0' end) <> '0' or min(case when attribute_id = 9 then value else '0' end) <> '0' 

Esto mostrará a los clientes con un valor de ciudad o región. Si solo desea clientes con AMBOS attributes, en la cláusula having, cambie el OR a AND.