I'm porting simple expense database to postgres and I got stuck on view using GROUP BY
and multiple JOIN
clauses. I think pgsql wants me to use all the tables in GROUP BY
statement. (Table schemas at the end. Note that columns account_id
, receiving_account_id
, place
may be NULL
and operation can have 0 tags).
Here is original CREATE
query:
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
I did some updates and my current query is:
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;
Here I get Column 'x' must appear in GROUP BY clause
errors as I add listed ones:
GROUP BY COALESCE(ot.operation_id, op.id), op.id, c.name, ac2.name, ac.name, t.name, p.name
When I add p.name
column I get Column 'p.name' is defined more than once error.
How do I fix that?
Table schemas:
CREATE TABLE operation
(
id integer NOT NULL,
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 operation_pk PRIMARY KEY (id),
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
)
Aucun commentaire:
Enregistrer un commentaire