How to GROUP BY?

[johannbuscail]

Hello, I was wondering if there was a way with supabase js to do a group by in the sql query without using an rpc ?

Example of how i would imagine it:

const { data: projects, error } = await supabase
		.from("landing_pages")
		.select(`project_id, projects (id, name, description)`)
		.group('project_id');

[GaryAustin1]

No. You need to use a view or rpc call to a postgres function and return what you want.

[steve-chavez]

PostgREST v12 supports aggregate functions, this is not yet supported on Supabase but there will be an announcement next week.

[TomasSestak]

Is this available already?

[GaryAustin1]

If you install the latest database version from infrastructure.

[jnorris441]

Error from PostgREST 12 on Supabase: "Use of aggregate functions is not allowed"

Hopefully they will enable aggregate functions soon

[steve-chavez]

It needs to be enabled manually.

ALTER ROLE authenticator SET pgrst.db_aggregates_enabled = 'true';

NOTIFY pgrst, 'reload config';

See https://postgrest.org/en/v12.0/references/configuration.html#db-aggregates-enabled

[sethtjf]

For anyone planning to do this, I kept seeing "Use of aggregate functions is not allowed" even after running this command. Restarting my instance fixed it.

[steve-chavez]

Restarting my instance fixed it.

Restarting shouldn't be necessary. There was a missing statement in the above snippet, just updated it to include NOTIFY pgrst, 'reload config'.

[wyozi]

Is there a way to enable them only for the service role? Trying to enable them on service_role only gave me the same error

[stefan-girlich]

What is the best way to include this in my codebase? Is it seed.sql?