You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Boiled down to group by behaviours when dealing with views.
Case 1: All columns specified in the view must be a part of the group by statement. Or the DDL creation will fail.
ex:
create table foo(n1 int, n2 int);
create view v1 as select n1,n2 from foo group by n1;
ERROR: column "foo.n2" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: create view v1 as select n1,n2 from foo group by n1;
Case 2: However there’s an exception to this. If all the columns which are a part of the primary key of the tables used for the view are present in the group by clause, DDL creation will go through.
ex:
alter table foo add constraint foo_pk primary key(n1);
create view v1 as select n1,n2 from foo group by n1;
CREATE VIEW
If the view is created using this exception, the correct DDL will fall under rule.sql and the incorrect DDL will fall under view.sql rule.sql
CREATE OR REPLACE VIEW public.v1 AS
SELECT foo.n1,
foo.n2
FROM public.foo
GROUP BY foo.n1;
view.sql
CREATE VIEW public.v1 AS
SELECT
NULL::integer AS n1,
NULL::integer AS n2;
Case 3: If the group by clause has all the columns mentioned in the view, the DDLs will fall under expected files.
create view v2 as select n1,n2 from foo group by n1,n2;
CREATE VIEW
view.sql
CREATE VIEW public.v2 AS
SELECT foo.n1,
foo.n2
FROM public.foo
GROUP BY foo.n1, foo.n2;
The text was updated successfully, but these errors were encountered:
Boiled down to
group by
behaviours when dealing with views.Case 1: All columns specified in the view must be a part of the group by statement. Or the DDL creation will fail.
ex:
Case 2: However there’s an exception to this. If all the columns which are a part of the primary key of the tables used for the view are present in the group by clause, DDL creation will go through.
ex:
If the view is created using this exception, the correct DDL will fall under
rule.sql
and the incorrect DDL will fall underview.sql
rule.sql
view.sql
Case 3: If the group by clause has all the columns mentioned in the view, the DDLs will fall under expected files.
view.sql
The text was updated successfully, but these errors were encountered: