samedi 2 janvier 2016

How do I port query with GROUP BY clause to PostgreSQL?

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