Parsing intervals fails with `invalid syntax`

[marcusirgens]

Version

1.10.0

What happened?

Generated code using the interval type in Postgres causes invalid syntax errors.

I've created a sample application to test the behaviour (as I am not quite sure how to run tests with an actual database in this repository). I've essentially used the internal/endtoend/testdata/interval/stdlib configuration as is, and it fails to both insert and read from the table.

The errors I receive are:

cannot convert 1000000 to Interval

and

sql: Scan error on column index 1, name "interval": converting driver.Value type string ("00:05:00") to a int64: invalid syntax

Full code to reproduce the issue is available at github.com/marcusirgens/sqlc-interval-debug.

Relevant log output

go test ./... -tags withdb \
        -database-url "postgres://sqlc:sqlc@localhost:15432/sqlc" \
        -timeout 30s
--- FAIL: TestQueries (0.02s)
    query_test.go:22: connecting to postgres://sqlc:sqlc@localhost:15432/sqlc
    --- FAIL: TestQueries/Insert_various_Foos (0.00s)
        query_test.go:57: inserting Foo: cannot convert 1000000 to Interval
        query_test.go:57: inserting Foo: cannot convert 15000000 to Interval
        query_test.go:57: inserting Foo: cannot convert 1000000000 to Interval
        query_test.go:57: inserting Foo: cannot convert 15000000000 to Interval
        query_test.go:57: inserting Foo: cannot convert 60000000000 to Interval
        query_test.go:57: inserting Foo: cannot convert 900000000000 to Interval
        query_test.go:57: inserting Foo: cannot convert 3600000000000 to Interval
        query_test.go:57: inserting Foo: cannot convert 54000000000000 to Interval
    --- FAIL: TestQueries/List_all_Foos (0.00s)
        query_test.go:65: fetching foos: sql: Scan error on column index 1, name "interval": converting driver.Value type string ("00:05:00") to a int64: invalid syntax
FAIL
FAIL    github.com/marcusirgens/sqlc-interval-debug 0.265s
?       github.com/marcusirgens/sqlc-interval-debug/go  [no test files]
FAIL
make: *** [test] Error 1

Database schema

CREATE TABLE foo (bar bool not null, "interval" interval not null);

INSERT INTO foo (bar, "interval")
VALUES (true, interval '5' minute),
       (true, interval '10' minute),
       (true, interval '15' minute),
       (true, interval '1' hour),
       (true, interval '1' day)

SQL queries

-- name: Get :many
SELECT bar, "interval" FROM foo LIMIT $1;

-- name: Insert :exec
INSERT INTO foo (bar, interval) VALUES ($1, $2);

Configuration

{
  "version": "1",
  "packages": [
    {
      "path": "go",
      "name": "querytest",
      "schema": "query.sql",
      "queries": "query.sql"
    }
  ]
}

Playground URL

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

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[patrickdevivo]

I just encountered the same issue, and "resolved" it for now by supplying the following override:

    overrides:
      - column: "table_name.column_name"
        go_type: "database/sql.NullString"

[dhermes]

It seems https://pkg.go.dev/github.com/jackc/pgtype@v1.12.0#Interval would also be appropriate here?