¿Cómo puedo consultar el puerto con la cláusula GROUP BY a PostgreSQL?

Estoy portando una database simple de gastos a Postgres y me quedé atrapada en una vista usando GROUP BY y varias cláusulas JOIN . Creo que Postgres quiere que use todas las tablas en la cláusula GROUP BY .

La definición de tabla está al final. Tenga en count que las columnas account_id , receiving_account_id y place pueden ser NULL y una operation puede tener 0 tags.

Original statement CREATE

 CREATE VIEW details AS SELECT op.id, op.name, c.name, CASE --amountsign WHEN op.receiving_account_id IS NOT NULL THEN CASE WHEN op.account_id IS NULL THEN '+' ELSE '=' END ELSE '-' END || ' ' || printf("%.2f", op.amount) || ' zł' AS amount, CASE --account WHEN op.receiving_account_id IS NOT NULL THEN CASE WHEN op.account_id IS NULL THEN ac2.name ELSE ac.name || ' -> ' || ac2.name END ELSE ac.name END AS account, t.name AS type, CASE --date WHEN op.time IS NOT NULL THEN op.date || ' ' || op.time ELSE op.date END AS date, p.name AS place, GROUP_CONCAT(tag.name, ', ') AS tags FROM operation op LEFT JOIN category c ON op.category_id = c.id LEFT JOIN type t ON op.type_id = t.id LEFT JOIN account ac ON op.account_id = ac.id LEFT JOIN account ac2 ON op.receiving_account_id = ac2.id LEFT JOIN place p ON op.place_id = p.id LEFT JOIN operation_tag ot ON op.id = ot.operation_id LEFT JOIN tag ON ot.tag_id = tag.id GROUP BY IFNULL (ot.operation_id, op.id) ORDER BY date DESC 

Consulta actual en Postgres

Hice algunas actualizaciones y mi statement actual es:

 BEGIN TRANSACTION; CREATE VIEW details AS SELECT op.id, op.name, c.name, CASE --amountsign WHEN op.receiving_account_id IS NOT NULL THEN CASE WHEN op.account_id IS NULL THEN '+' ELSE '=' END ELSE '-' END || ' ' || op.amount || ' zł' AS amount, CASE --account WHEN op.receiving_account_id IS NOT NULL THEN CASE WHEN op.account_id IS NULL THEN ac2.name ELSE ac.name || ' -> ' || ac2.name END ELSE ac.name END AS account, t.name AS type, CASE --date WHEN op.time IS NOT NULL THEN to_char(op.date, 'DD.MM.YY') || ' ' || op.time ELSE to_char(op.date, 'DD.MM.YY') END AS date, p.name AS place, STRING_AGG(tag.name, ', ') AS tags FROM operation op LEFT JOIN category c ON op.category_id = c.id LEFT JOIN type t ON op.type_id = t.id LEFT JOIN account ac ON op.account_id = ac.id LEFT JOIN account ac2 ON op.receiving_account_id = ac2.id LEFT JOIN place p ON op.place_id = p.id LEFT JOIN operation_tag ot ON op.id = ot.operation_id LEFT JOIN tag ON ot.tag_id = tag.id GROUP BY COALESCE (ot.operation_id, op.id) ORDER BY date DESC; COMMIT; 

Aquí Column 'x' must appear in GROUP BY clause errores de la Column 'x' must appear in GROUP BY clause cuando agrego los enumerados:

 GROUP BY COALESCE(ot.operation_id, op.id), op.id, c.name, ac2.name, ac.name, t.name, p.name 

Cuando p.name columna p.name me sale la Column 'p.name' is defined more than once error. ¿Cómo arreglo eso?

Definición de tabla

 CREATE TABLE operation ( id integer NOT NULL PRIMARY KEY, name character varying(64) NOT NULL, category_id integer NOT NULL, type_id integer NOT NULL, amount numeric(8,2) NOT NULL, date date NOT NULL, "time" time without time zone NOT NULL, place_id integer, account_id integer, receiving_account_id integer, CONSTRAINT categories_transactions FOREIGN KEY (category_id) REFERENCES category (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT transactions_accounts FOREIGN KEY (account_id) REFERENCES account (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT transactions_accounts_second FOREIGN KEY (receiving_account_id) REFERENCES account (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT transactions_places FOREIGN KEY (place_id) REFERENCES place (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT transactions_transaction_types FOREIGN KEY (type_id) REFERENCES type (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ); 

Como @Andomar ya provisto : la mayoría de los RDBMS requieren agrupar por cada columna que aparece desagregada, en cualquier otro lugar de la consulta (incluida la list SELECT , pero también en la cláusula WHERE etc.)

  • PGError: ERROR: agregados no permitidos en la cláusula WHERE en una consulta AR de un object y sus has_many objects

El estándar SQL también define que las expresiones en la cláusula GROUP BY también cubrirán las expresiones funcionalmente dependientes. Postgres implementó que la columna PK cubre todas las columnas de la misma tabla .

  • PostgreSQL – cláusula GROUP BY

Así que op.id cubre toda la tabla y esto debería funcionar para su consulta actual:

 GROUP BY op.id, c.name, 5, t.name, p.name 

5 es una reference posicional a la list SELECT , que también está permitida en Postgres. Es solo una notación taquigráfica para repetir la expresión larga:

 CASE WHEN op.receiving_account_id IS NOT NULL THEN CASE WHEN op.account_id IS NULL THEN ac2.name ELSE ac.name || ' -> ' || ac2.name END ELSE ac.name END 
  • Concatenar múltiples filas de resultados de una columna en una, agrupar por otra columna
  • Seleccione la primera fila en cada grupo GROUP BY?

Derivo de sus nombres que tiene una relación: m entre operation y tag , implementada con operation_tag . El rest de las combinaciones no parecen multiplicar las filas, por lo que sería más eficiente agregar las tags por separado, como @Andomar insinuado, simplemente obtenga la lógica correcta.

Esto debería funcionar:

 SELECT op.id , op.name , c.name , CASE -- amountsign WHEN op.receiving_account_id IS NOT NULL THEN CASE WHEN op.account_id IS NULL THEN '+' ELSE '=' END ELSE '-' END || ' ' || op.amount || ' zł' AS amount , CASE -- account WHEN op.receiving_account_id IS NOT NULL THEN CASE WHEN op.account_id IS NULL THEN ac2.name ELSE ac.name || ' -> ' || ac2.name END ELSE ac.name END AS account , t.name AS type , to_char(op.date, 'DD.MM.YY') || ' ' || op.time AS date -- see below , p.name AS place , ot.tags FROM operation op LEFT JOIN category c ON op.category_id = c.id LEFT JOIN type t ON op.type_id = t.id LEFT JOIN account ac ON op.account_id = ac.id LEFT JOIN account ac2 ON op.receiving_account_id = ac2.id LEFT JOIN place p ON op.place_id = p.id LEFT JOIN ( SELECT operation_id, string_agg(t.name, ', ') AS tags FROM operation_tag ot LEFT JOIN tag t ON t.id = ot.tag_id GROUP BY 1 ) ot ON op.id = ot.operation_id ORDER BY op.date DESC, op.time DESC ; 

Aparte

Puedes replace:

 CASE --date WHEN op.time IS NOT NULL THEN to_char(op.date, 'DD.MM.YY') || ' ' || op.time ELSE to_char(op.date, 'DD.MM.YY') END AS date 

con este equivalente más corto:

 concat_ws(' ', to_char(op.date, 'DD.MM.YY'), op.time) AS date 

Pero como ambas columnas están definidas como NOT NULL , puedes simplificar para:

 to_char(op.date, 'DD.MM.YY') || ' ' || op.time AS date 

Ten cuidado con tu ORDER BY tienes al less una columna de input también llamada date . Si utiliza el nombre no calificado, se referirá a la columna de salida , que es lo que quiere (como se aclara en el comentario). Detalles:

  • PostgreSQL: ¿Cómo devolver filas con respecto a una fila encontrada (resultados relativos)?

Sin embargo , orderar por la representación de text no se orderaría de acuerdo a su línea de time correctamente. Ordenar por valores originales en su lugar como se sugiere en mi consulta anterior.

La mayoría de las bases de datos requieren que agrupe group by cada columna que aparece desagregada en la select . Unaggregated significa que no está envuelto en un agregado como min , max o string_agg . Por lo tanto, necesitaría agrupar: op.id, op.name, c.name, op.receiving_account_id, ... , etc.

El motivo de este requisito es que la database debe determinar un valor para el grupo. Al agregar la columna al group by cláusula, usted confirma que cada fila en el grupo tiene el mismo valor. Para otros grupos, debe especificar qué valor usar con un agregado. La exception es MySQL, que solo elige un valor arbitrario si no haces una elección consciente.

Si su group by solo desea crear una list de tags, puede moverla a una subconsulta:

 left join ( select id , string_agg(tag.name, ', ') tags from tag group by id ) t on ot.tag_id = t.id 

Y puede evitar un grupo muy largo para la consulta externa.