AVG should be nullable

[odannyc]

Version

1.14.0

What happened?

I have the following query:

SELECT AVG(rating)
FROM product_ratings
WHERE product_id = 1234;

This generates the following go code:

var avg string
err := row.Scan(&avg)

Notice how it uses a string for avg. I would like it to use an int, so I cast it like so:

SELECT AVG(rating)::int
FROM product_ratings
WHERE product_id = 1234;

Which generates the following code:

var column_1 int32
err := row.Scan(&column_1)

Now, my issue is that the product_ratings table might not always have entries for all products so if the AVG(rating) statement returns NULL for a specific product, I get the following error:

sql: Scan error on column index 0, name "avg": converting NULL to int32 is unsupported

Is there a way to force sqlc to generate sql.NullInt32 instead of just int32?

Relevant log output

sql: Scan error on column index 0, name "avg": converting NULL to int32 is unsupported

Database schema

CREATE TABLE product_ratings
(
  rating      smallint NOT NULL CHECK ( score <= 5 AND score >= 1 ),
  product_id  int      NOT NULL REFERENCES products (id)
);

CREATE TABLE products
(
   id    serial PRIMARY KEY,
   name  VARCHAR(255)  NOT NULL
);

SQL queries

SELECT AVG(rating)::int
FROM product_ratings
WHERE product_id = 1234;

Configuration

version: "1"
packages:
  - name: "sql"
    path: "./internal/store/sql"
    queries: "./internal/store/sql/queries/"
    schema: "./internal/store/sql/migrations/"
    engine: "postgresql"
    emit_db_tags: true
    emit_prepared_queries: true
    emit_interface: false
    emit_exact_table_names: false
    emit_empty_slices: true
    emit_json_tags: true
    json_tags_case_style: "camel"

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[eullerpereira94]

First things first, the avg() aggregate function returns a numeric value. The standard Go library doesn't have a decimal type that can handle it, so consider using pgx/v4 in the sql_package key in your config file, because the jackc/pgx package does have a type that can handle numeric values, because sqlc defaults numeric fields to be generated as string when using the standard library. Second thing, the avg() only returns null if, and only if, all the values provided to it are null as well. Which makes sense, since the null value represents things unknown at the time of reading. So, if none of your products are rated yet (which can't be the case here with the not null constraint), or your products table is empty, then the average value of the ratings can't be known, making the only acceptable output by the function to be null. Now, one way you can guarantee that there won't be a null value returned by the average is to coalesce the rating column like this: coalesce(rating, 0).

[kyleconroy]

I think @eullerpereira94 summed it up better than I could.

[gordonklaus]

Please reopen this issue. The title is correct (even if it didn't end up mattering for the OP): the result of avg can be null (if its argument is nullable). Sqlc should use a nullable types for the result of avg.

[20manas]

Related issues: https://github.com/sqlc-dev/sqlc/issues/3028, https://github.com/sqlc-dev/sqlc/issues/1208