Unable to encode slice of string alias

[psnehanshu]

Describe the bug I am getting error like this

failed to encode args[3]: unable to encode []sqlc.ApprovalRequestState{\"open\"} into text format for unknown type (OID 16477): cannot find encode

To Reproduce

// My Query
const getApprovalRequestsByOrg = `-- name: GetApprovalRequestsByOrg :many
SELECT id, state, deployment_name, org_id, min_approvals, expiration, title, body, created_at FROM approval_requests
WHERE org_id = $1 AND state = ANY($4::approval_request_state[])
ORDER BY created_at DESC
LIMIT $2 OFFSET $3
`

// The enum in question
type ApprovalRequestState string

const (
	ApprovalRequestStateDraft    ApprovalRequestState = "draft"
	ApprovalRequestStateOpen     ApprovalRequestState = "open"
	ApprovalRequestStateApproved ApprovalRequestState = "approved"
	ApprovalRequestStateRejected ApprovalRequestState = "rejected"
	ApprovalRequestStateClosed   ApprovalRequestState = "closed"
	ApprovalRequestStateReleased ApprovalRequestState = "released"
)

func (e *ApprovalRequestState) Scan(src interface{}) error {
	switch s := src.(type) {
	case []byte:
		*e = ApprovalRequestState(s)
	case string:
		*e = ApprovalRequestState(s)
	default:
		return fmt.Errorf("unsupported scan type for ApprovalRequestState: %T", src)
	}
	return nil
}

// Input struct
type GetApprovalRequestsByOrgParams struct {
	OrgID  uuid.UUID              `json:"org_id"`
	Limit  int                    `json:"limit"`
	Offset int                    `json:"offset"`
	States []ApprovalRequestState `json:"states"`
}

// The function to call the query
func (q *Queries) GetApprovalRequestsByOrg(ctx context.Context, arg GetApprovalRequestsByOrgParams) ([]ApprovalRequest, error) {
	rows, err := q.db.Query(ctx, getApprovalRequestsByOrg,
		arg.OrgID,
		arg.Limit,
		arg.Offset,
		arg.States,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ApprovalRequest
	for rows.Next() {
		var i ApprovalRequest
		if err := rows.Scan(
			&i.ID,
			&i.State,
			&i.DeploymentName,
			&i.OrgID,
			&i.MinApprovals,
			&i.Expiration,
			&i.Title,
			&i.Body,
			&i.CreatedAt,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

// The return type
type ApprovalRequest struct {
	ID             uuid.UUID            `json:"id"`
	State          ApprovalRequestState `json:"state"`
	DeploymentName string               `json:"deployment_name"`
	OrgID          uuid.UUID            `json:"org_id"`
	MinApprovals   pgtype.Int2          `json:"min_approvals"`
	Expiration     pgtype.Timestamptz   `json:"expiration"`
	Title          string               `json:"title"`
	Body           pgtype.Text          `json:"body"`
	CreatedAt      time.Time            `json:"created_at"`
}

Expected behavior It should return result

Actual behavior failed to encode args[3]: unable to encode []sqlc.ApprovalRequestState{"open"} into text format for unknown type (OID 16477): cannot find encode plan

Version

• Go: go version go1.24.1 linux/amd64]
• PostgreSQL: PostgreSQL 17.4 on x86_64-pc-linux-musl, compiled by gcc (Alpine 14.2.0) 14.2.0, 64-bit
• pgx: github.com/jackc/pgx/v5 v5.7.4

Additional context I am using SQLC to auto-generate functions.

My sqlc.yaml

version: "2"
sql:
  - engine: "postgresql"
    queries: "db/queries"
    schema: "db/migrations"
    gen:
      go:
        package: "sqlc"
        out: "db/sqlc"
        sql_package: "pgx/v5"
        emit_json_tags: true
        overrides:
          - db_type: "text"
            go_type: "string"
          - db_type: "integer"
            go_type: "int"
          - db_type: "boolean"
            go_type: "bool"
          - db_type: "timestamptz"
            go_type: "time.Time"
          - db_type: "uuid"
            go_type: "github.com/google/uuid.UUID"

I tried defining driver.Value on an alias, and it worked. But because I am using SQLC, I can't write it. It is auto-generated.

type X []ApprovalRequestState

// implement [driver.Valuer]
func (x X) Value() (driver.Value, error) {
 // ....
}

[psnehanshu]

A workaround is to change the condition in the query as follows

Old:

state = ANY($4::approval_request_state[])

New:

state::text = ANY($4::text[])

But then we lose type safety of enums

[jackc]

The underlying issue is this part of the error message: for unknown type (OID 16477)

You know that it is an array of your enum type, but pgx doesn't. So it has no idea how to encode the value. You need to register your custom types with pgx. See https://pkg.go.dev/github.com/jackc/pgx/[email protected]/pgtype#hdr-New_PostgreSQL_Type_Support.