PostgreSQL aggregate function any_value is not supported

[sgielen]

Version

1.25.0

What happened?

I have a query containing a GROUP BY clause, which uses ANY_VALUE (postgres v16+) to select any non-null value from a grouping, since (in my particular use-case) I know all values from a given grouping will be the same anyway. This allows for better performance than adding that column in the GROUP BY, too.

From the viewpoint of sqlc, ANY_VALUE could be considered an identity function. However, instead, it has name AnyValue and type interface{}.

Relevant log output

No response

Database schema

CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);

SQL queries

-- name: GetAnyAuthorByName :one
-- Return authors with name and id. If a name occurs twice, which is typical because
-- writing skill is genetic, just return any id for a given name.
SELECT name, ANY_VALUE(id) FROM authors
GROUP BY name;

-- name: GetAnyAuthorByNameExpected :one
-- Return authors with name and id. If a name occurs twice, which is typical because
-- writing skill is genetic, just return any id for a given name.
SELECT name, ANY_VALUE(id)::bigint AS id FROM authors
GROUP BY name;

Configuration

{
  "version": "2",
  "sql": [{
    "schema": "schema.sql",
    "queries": "query.sql",
    "engine": "postgresql",
    "gen": {
      "go": {
        "out": "db"
      }
    }
  }]
}

Playground URL

https://play.sqlc.dev/p/4b65a128607b6caf2c72886b3a69043e094fe5b62af686a0285350b03ea5a18c

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[kyleconroy]

I believe this is fixed if you use the database-backed analyzer. Here's how it looks using it.

https://play.sqlc.dev/p/e7c1e9b0b104770d0135b2c4942c8cb4cdd6f1f5fc5b984c8ea0a89740458bd6