sqlc.narg ignored with array cast

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

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"

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[])
);

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[])

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.

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

Hello everyone, any updates on this issue?

fix: let sqlc output nullable array

database/gendb: adapt to changes, improvements

Navigation Menu

Repository navigation

sqlc.narg ignored with array cast #1851

Version

What happened?

Relevant log output

Database schema

SQL queries

Configuration

Playground URL

What operating system are you using?

What database engines are you using?

What type of code are you generating?

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

sqlc.narg ignored with array cast #1851

Description

Version

What happened?

Relevant log output

Database schema

SQL queries

Configuration

Playground URL

What operating system are you using?

What database engines are you using?

What type of code are you generating?

Activity

angaz commented on Sep 28, 2022

dhermes commented on Dec 1, 2022

akhmadnurmuhammad commented on Dec 6, 2022

ekron commented on Aug 12, 2023

GerardRodes commented on Sep 8, 2023

kokhans commented on Jul 28, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions