sqlc.narg ignored with array cast

[angaz]

Version

1.15.0

What happened?

In the playground example I supplied, you can see that I have used both sqlc.arg and sqlc.narg when casting to TEXT[], but the generated code does not contain sql.NullString for the second argument as I expected.

Input:

-- name: batchUpsert :exec
INSERT INTO authors (id, name, bio)
VALUES (
  unnest(sqlc.arg(ids)::BIGINT[]),
  unnest(sqlc.arg(names)::TEXT[]),
  unnest(sqlc.narg(bios)::TEXT[])
) ON CONFLICT (id) DO UPDATE SET
	name = EXCLUDED.name,
	bio = EXCLUDED.bio;

Output:

type batchUpsertParams struct {
	Ids   []int64
	Names []string
	Bios  []string
}

Expected:

type batchUpsertParams struct {
	Ids   []int64
	Names []string
	Bios  []sql.NullString
}

If I take away the array part of the cast, so ::TEXT instead of ::TEXT[], it seems to work as expected, so I assume that this is a bug, and not intentional.

If there are any other way to have a dynamic batching system someone could recommend, I will give that a try, but so far, this is what I have.

Thanks

Relevant log output

No response

Database schema

No response

SQL queries

No response

Configuration

No response

Playground URL

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

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[angaz]

I solved my problem by overriding the generated types to use the pgtype package because these types are nullable. Then I create dependency inversion functions which take in the application types and converts them to the pgtype types.

So my sqlc.yaml looks something like this:

version: 2
sql:
  - engine: "postgresql"
    gen:
      go:
        sql_package: "pgx/v4"
        overrides:
          - db_type: "uuid"
            go_type: "github.com/jackc/pgtype.UUID"
          - db_type: "text"
            go_type: "github.com/jackc/pgtype.Text"

[dhermes]

Here is a slightly more focused hack. Introduce an alias type in the DB, e.g.

CREATE DOMAIN public.UUID_NULL_ALIAS AS UUID;
COMMENT ON DOMAIN public.UUID_NULL_ALIAS IS
  'Alias for UUID; introduced to workaround sqlc bug (1851)';

Configure via

---
version: "2"
overrides:
  go:
    overrides:
      - go_type: github.com/google/uuid.NullUUID
        db_type: uuid_null_alias
        nullable: true
      - go_type: github.com/google/uuid.NullUUID
        db_type: uuid_null_alias
        nullable: false
# ...

and then use

-- name: BatchInsert :exec
INSERT INTO
  widget (id, optional)
VALUES (
  UNNEST(sqlc.arg(id)::UUID[]),
  UNNEST(sqlc.narg(optional)::UUID_NULL_ALIAS[])
);

[akhmadnurmuhammad]

still looking forward for this issue, i have same problem here but still not working, FYI i'm using version 1 for configuration

SOLVED
try adding this at sqlc.yaml for overrides
overrides:
- db_type: "pg_catalog.timestamp"
go_type: "database/sql.NullTime"

and the queries like this
unnest(sqlc.narg('created_at')::TIMESTAMP[])

[ekron]

Just to add another example.

With this sql:

CREATE TABLE example (
  name text NOT NULL
);

-- name: FailingWithNullableArrayTextParam :many
SELECT * FROM example
WHERE example.name = ANY (sqlc.narg('expected_nullable_array')::text[]);

-- name: WorkingWithNullableSingleTextParam :many
SELECT * FROM example
WHERE example.name = sqlc.narg('is_nullable')::text;

If I cast a sqlc.narg to an array type I expect it to result in a nullable param. But the actual param is not nullable:

func (q *Queries) FailingWithNullableArrayTextParam(ctx context.Context, expectedNullableArray []string) ([]string, error) { ...

It works for a param cast to a non-array.

You can see the result here:

https://play.sqlc.dev/p/33b38fa273e0c059979f62e1dfc95c7a1edd76f8ef209e21f04fa97abf71ecac

I have tried with enums and other types, but the bug remains the same.

[GerardRodes]

I wouldn't add the pgx/v4 label, it happens in every scenario:
https://play.sqlc.dev/p/8768da94721f7c53e21b0179cf1644be3e96d0ef5209da9edafe87342f162c1a

unnest(sqlc.narg(bios)::TEXT[]) becomes Bios []string and should be some kind of nulable string