0

Lets say I have two tables. One of them references the other in a one-to-many relationship:

CREATE TABLE t (
  id SERIAL PRIMARY KEY
, name text
);

INSERT INTO t (name) VALUES ('one'), ('two'), ('three');

CREATE TABLE y (
  id SERIAL PRIMARY KEY
, tid INTEGER REFERENCES t
, amount INTEGER
);

INSERT INTO y (tid, amount) VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 1),
(3, 1);

I can query data from these tables in order to get t rows matching a certain condition set on t and y:

SELECT row_to_json(t.*) as "t_nogroup" FROM t
JOIN y ON t.id = y.tid
WHERE t.id = 1
AND y.amount > 1;

This yields

t_nogroup
=========
{"id":1,"name":"one"}
{"id":1,"name":"one"}

Which works, yes, but contains duplicates. I can filter out the duplicates by using a GROUP BY:

SELECT row_to_json(t.*) as "t_group" FROM t
JOIN y ON t.id = y.tid
WHERE t.id = 1
AND y.amount > 1
GROUP BY t.id;

Making:

t_group
=======
{"id":1,"name":"one"}

Great! However, when I try to exchange t and y for non-materialized VIEWs:

CREATE VIEW vt AS SELECT t.* FROM t;
CREATE VIEW vy AS SELECT y.* FROM y;

And run the same queries with the new VIEWs

SELECT row_to_json(vt.*) as "view_t_nogroup" FROM vt
JOIN vy ON vt.id = vy.tid
WHERE vt.id = 1
AND vy.amount > 1;

SELECT row_to_json(vt.*) as "view_t_group" FROM vt
JOIN vy ON vt.id = vy.tid
WHERE vt.id = 1
AND vy.amount > 1
GROUP BY vt.id;

I then get the error for the second query:

ERROR: column "vt.*" must appear in the GROUP BY clause or be used in an aggregate function

Why does this happen? Whatever logic that saw that * contained id when using the table t should still work for the view vt, right?

Fiddle link: http://sqlfiddle.com/#!17/26b0e/5

I'm running PostgreSQL 9.6. I'm also aware that this is a contrived example, but I am running into this error in a very similar one to many relation where multiple GROUP BY statements are needed and so just using DISTINCT isn't an option, like it would be for this example.

  • 3
    Because it knows that table t has (id) as the primary key but it can't infer the same for view vt. – ypercubeᵀᴹ Jan 10 '18 at 20:25
  • Hmm, I see. Is there any way to work around this? – Avery Jan 10 '18 at 20:29
3

The problem appears because Postgres knows that table t has (id) as the primary key - so we can use GROUP BY t.id and have more columns from that table in the SELECT list without aggregating them - but it can't infer the same for view vt.

For the examples provided, you can rewrite them as not to use GROUP BY at all. Since you don't need any column from the second joined table in the select list, you can convert the JOIN to an EXISTS subquery, i.e. convert the join to a semi-join. Now both the tables and views semijoin work without errors:

SELECT row_to_json(t.*) as "t_group" FROM t
WHERE t.id = 1 
  AND EXISTS 
      ( SELECT 1 
        FROM y 
        WHERE t.id = y.tid
          AND y.amount > 1 
      ) ;

SELECT row_to_json(vt.*) as "t_group" FROM vt
WHERE vt.id = 1 
  AND EXISTS 
      ( SELECT 1 
        FROM vy 
        WHERE vt.id = vy.tid
          AND vy.amount > 1 
      ) ;
  • Info was very useful, thanks. Unfortunately not able to use this example due to the OP being more generalized than my actually application, but the knowledge was very helpful – Avery Jan 31 '18 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.