Timestamp arrays do not scan correctly

[stephanievance29]

Version

1.10.0

What happened?

When I have a timestamp array in my table and I want to bring the data back, I expect the results to be correctly mapped to data types in go. When I actually run my code that utilizes the sqlc generated code, I get an error that scanning is not supported for arrays of time.Time. In order to get this to work in my project, I had to not use sqlc for this query and instead have my sql query as a string in my code and write my own scanner for the results.

I had opened this ticket previously https://github.com/kyleconroy/sqlc/issues/1533 and was asked to recreate this issue in the playground. I tried but it is not able to be reproduced in the playground since I am not able to add a file that utilizes the generated code. The error does not occur when the sqlc code is generated, just at runtime when it is actually processing results.

I have included the full error message I get in the relevant log output section. The database schema and sql queries sections have dummy tables/queries in them that I used to recreate this issue in a smaller separate project to confirm this issue wasn't do to something else in my main project. I am running on WSL2 so I checked both Windows and Linux below.

Relevant log output

sql: Scan error on column index 5, name "my_times": pq: parsing array element index 0: pq: scanning to time.Time is not implemented; only sql.Scanner

Database schema

CREATE TABLE temp (
    id int not null,
    item text,
    flag boolean,
    places int[],
    start_time timestamp,
    my_times timestamp[]
);

SQL queries

-- name: InsertRow :one
insert into temp (
    id,
    item,
    flag,
    places,
    start_time,
    my_times
) values ($1, $2, $3, $4, $5, $6)
RETURNING *;

-- name: UpdateRow :one
update temp
set places[$1] = $2
where id=$3
RETURNING *;

-- name: SelectRow :one
select * from temp limit one;

Configuration

{
    "version": "1",
    "packages": [{
      "schema": "schema.sql",
      "queries": "query.sql",
      "name": "data",
      "path": "./internal/data"
    }]
  }

Playground URL

https://play.sqlc.dev/p/8a265f85bbe8d426846a80773711b8114a496017ff5a948635033de7a87324cf

What operating system are you using?

Linux, Windows

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[kyleconroy]

Thank you for such a great bug report. I believe this exact issue is tracked upstream as https://github.com/lib/pq/issues/738. You may be able to get this to work by creating a custom type for the time array and using type overrides.

[tyro-jason]

Hi Kyle - similar issue but with overrides... It complains about an override of "time.Time" because it's expecting the whole "github.com/blah/time.Time" For built in go packages it doesn't seem to work?

Package override `go_type` specifier "time.Time" is not the proper format, expected 'package.type', e.g. 'github.com/segmentio/ksuid.KSUID'

[earthboundkid]

@tyro-jason I've run into that bug too. A quick fix seems to be changing from "go_type": "time.Time" to

"go_type": {
        "import": "time",
        "type": "Time"
      },