sqlc.yaml configuration does not override postgresql interval to time.Duration

php editor Youzi is here to introduce to you a question about sqlc.yaml configuration. When using postgresql, we often need to use time interval (time.Duration) to represent a period of time. However, sometimes we find that the time interval configuration in the sqlc.yaml configuration file does not take effect, but is overridden by the default configuration. How to solve this problem? Please continue reading this article and we will give detailed answers.

Question content

I found a problem with the sqlc codegen application. In case, when I need interval (postgresql) field, sqlc generates an object with int64 field. This solution looks broken and produces an error when scanning the line: errorf("cannot conversion %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
}

The problem was quickly fixed by setting this field to type time.duration. duration and the code starts working, but this code is code generated and looks like a bad decision.

When trying to override the type via the sqlc.yaml configuration I get nothing, the object is still being created of type int64. Where did I go wrong and how can I fix this?

Solution

In Supported types, you will see pg_catalog.interval is also supported by interval in postgres one of the values.

So if you just want to use time.duration instead of int64, you need to change the overrides part to:

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

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

The above is the detailed content of sqlc.yaml configuration does not override postgresql interval to time.Duration. For more information, please follow other related articles on the PHP Chinese website!