Decimal Type Overrides Only Work for `AVG` Function, Not for Direct Column Reference

[timjonesdev]

trafficstars

Version

1.14.0

What happened?

I am using numeric and decimal types, and have attempted to override the Go type that is generated using github.com/shopspring/decimal. The appropriate Go type is generated when using AVG(my_decimal_type), but not when referencing my_decimal_type directly.

Here is a sqlc playground link that illustrates the issue: https://play.sqlc.dev/p/737a16bd07f63be404f3cea2fefc6846ad0f1c048b4874c3d005220316826052

Relevant log output

No response

Database schema

-- Example queries for sqlc
CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  my_numeric_type numeric,
  my_decimal_type decimal
);

SQL queries

-- name: GetAuthor :one
SELECT my_numeric_type::numeric,
my_decimal_type 
FROM authors;

-- name: GetAverage :one
SELECT AVG(my_numeric_type) FROM authors;

-- name: ListAuthors :many
SELECT * FROM authors
ORDER BY name;

Configuration

{
  "version": "2",
  "sql": [
    {
      "schema": "/",
      "queries": "/",
      "engine": "postgresql",
      "gen": {
        "go": {
          "package": "contestdb",
		  "out": "./",
          "emit_interface": true,
          "overrides": [
            {
              "go_type": "github.com/jackc/pgtype.UUID",
              "db_type": "uuid"
            },
            {
              "go_type": "github.com/jackc/pgtype.Interval",
              "db_type": "interval"
            },
            {
              "go_type": "github.com/shopspring/decimal.Decimal",
              "db_type": "numeric"
            }
          ]
        }
      }
    }
  ]
}

Playground URL

https://play.sqlc.dev/p/737a16bd07f63be404f3cea2fefc6846ad0f1c048b4874c3d005220316826052

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[kyleconroy]

Okay, this is an extremely annonying answer, but you need to use a different type for the override. pg_catalog.numeric works while numeric does not.

{
  "version": "2",
  "sql": [
    {
      "schema": "/",
      "queries": "/",
      "engine": "postgresql",
      "gen": {
        "go": {
          "package": "contestdb",
		  "out": "./",
          "emit_interface": true,
          "overrides": [
            {
              "go_type": "github.com/jackc/pgtype.UUID",
              "db_type": "uuid"
            },
            {
              "go_type": "github.com/jackc/pgtype.Interval",
              "db_type": "interval"
            },
            {
              "go_type": "github.com/shopspring/decimal.Decimal",
              "db_type": "pg_catalog.numeric"
            },
            {
              "go_type": "github.com/shopspring/decimal.Decimal",
              "db_type": "pg_catalog.numeric",
			  "nullable": true
            }
          ]
        }
      }
    }
  ]
}

https://play.sqlc.dev/p/47390ac6fd2cfd238343cd9fecf29f9b107f115775af0ca64b4b4b01eae93237

Obviously there's no way that you'd be able to know that, so I'm going to keep this issue open (or create a new one) to fix this.

[kyleconroy]

Tracking in https://github.com/sqlc-dev/sqlc/issues/2762