sqlc.yaml config isn't going to override postgresql interval to time.Duration

Question

I've found a problem with an sqlc codegen app. In case, when I need an interval (postgresql) field, sqlc generates an object with int64 field. This solution looks broken and creates an error while scanning a row: Errorf("cannot convert %v to Interval", value)

sqlc.yaml:

version: "2"
overrides:
  go:
    overrides:
      - db_type: "interval"
        engine: "postgresql"
        go_type:
          import: "time"
          package: "time"
          type: "https://pkg.go.dev/time#Duration"
sql:
  - queries: "./sql_queries/raffle.query.sql"
    schema: "./migrations/001-init.sql"
    engine: "postgresql"
    gen:
     go:
        package: "raffle_repo"
        out: "../repo/sql/raffle_repo"
        sql_package: "pgx/v4"

schema.sql:

create table windowrange
(
    id        serial    primary key,
    open      timestamp not null ,
    duration  interval not null,
    created_at timestamp default now(),
    updated_at timestamp default now(),
    raffle_id integer not null
        constraint raffle_id
            references raffle
            on delete cascade
);

generated model:

type Windowrange struct {
    ID        int32
    Open      time.Time
    Duration  int64
    CreatedAt sql.NullTime
    UpdatedAt sql.NullTime
    RaffleID  int32
}

It was quickly fixed by making this field of type time.Duration and code becomes working, but this code were codegened and it looks like bad decision.

While trying to override types by an sqlc.yaml config I have nothig, object is still creating of int64 type. Where I'm wrong and how can I fix this?

score 0 · Accepted Answer · answered Dec 26 '22 at 23:10

In the supported types, you will see that pg_catalog.interval is also one of the supported values for interval in Postgres.

So, if you just want to use time.Duration instead of int64, you need to change your overrides section to:

overrides:
  go:
    overrides:
      - db_type: "pg_catalog.interval"
        engine: "postgresql"
        go_type:
          import: "time"
          type: "Duration"

Tip: if it doesn't work for the most obvious data type, you can try another one.

sqlc.yaml config isn't going to override postgresql interval to time.Duration

1 Answers1