Executing query that takes an array of a custom enum type with pgx/v4 fails

[jvatic]

Version

Other

What happened?

Executing query that takes an array of a custom enum type with pgx/v4 fails with:

Cannot encode []main.WidgetType into oid 16393 - []main.WidgetType must implement Encoder or be converted to a string

Version: tested both with v1.8.0 and the latest main (6ee39cb)

Relevant log output

No response

Database schema

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY
);

CREATE TYPE widget_type AS ENUM('a', 'b', 'c');

CREATE TABLE widgets (
  id      BIGSERIAL PRIMARY KEY,
  user_id BIGSERIAL NOT NULL REFERENCES users (id),
  name    text      NOT NULL,
  "type"  widget_type NOT NULL
);

SQL queries

-- name: ListWidgets :many
SELECT * FROM widgets
WHERE "type" = ANY(@type::widget_type[])
AND user_id = @user_id;

Configuration

{
  "version": "1",
  "packages": [
    {
      "path": "db",
      "engine": "postgresql",
      "sql_package": "pgx/v4",
      "schema": "query.sql",
      "queries": "query.sql"
    }
  ]
}

Playground URL

https://play.sqlc.dev/p/4109299ab81a98ca6e06d6389d5a50aed1b8ffa8a4abfe9b761110f885afd3d6

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[jvatic]

A workaround for this issue is to convert the column being compared into text:

-- name: ListWidgets :many
SELECT * FROM widgets
WHERE "type"::text = ANY(@type::text[])
AND user_id = @user_id;

[josharian]

I also hit this, and am motivated to fix it.

Another workaround is to use pq.Array, even though everything else is using pgx. Unfortunately, this requires editing the generated code, and it pulls in the entire pq dependency.

It seems like the easiest fix may be to extract pq.Array into a standalone component. I have checked, and array.go and array_test.go have no dependencies and compile independently of the rest of the package, and the licensing is permissive. (And I could ask Blake directly if necessary.)

sqlc could copy the array code in when it detects it is needed. (Or it could try to emit only the parts it really needs, but that's probably unnecessary.) Then sqlc could use that code unilaterally, including in pq mode.

If that approach sounds good, I'd be happy to send a PR, but I'd need a few pointers. I found the spot where pq.Array gets emitted, but I'm not sure how best to conditionally emit the helper code, because that involves passing some state around. Or maybe it'd be OK to always emit some static helper code, in helper.go or array.go?

[josharian]

Scratch that, it doesn't quite compile cleanly out of the box. But this does: https://github.com/josharian/pqarray.

[josharian]

Another workaround is to use a column go_type override with an appropriate type from github.com/josharian/pqarray, and then write your own conversion helpers. Ugly, but still retains a bit of type safety.

[davidspiess]

It's enough to implement a DecodeText and EncodeText interface and call the underlying pgtype.TextArray implementation.

Example

type Topic string

type Topics []Topic

const (
    Family          Topic = "family"
    Hiking          Topic = "hiking"
    Bicycle         Topic = "bicycle"
)

func (t *Topics) DecodeText(ci *pgtype.ConnInfo, src []byte) error {
    var dec pgtype.TextArray
    if err := dec.DecodeText(ci, src); err != nil {
        return err
    }
    for _, el := range dec.Elements {
        *t = append(*t, Topic(el.String))
    }
    return nil
}

func (t Topics) EncodeText(ci *pgtype.ConnInfo, buf []byte) ([]byte, error) {
    var enc pgtype.TextArray
    if err := enc.Set(t); err != nil {
        return nil, err
    }
    return enc.EncodeText(ci, buf)
}

[spikecdc]

I encountered the same problem, has this been fixed?

Sql Queries

-- name: UpdateAuthnStepLimit :exec
UPDATE authn_step_limits SET status = $1, failure_attempts = $2 WHERE user_id = $3 AND step_type = ANY($4::authn_step_type[]);

Configuration

version: 2
sql:
  - schema: "./migrations"
    queries: "./queries"
    engine: "postgresql"
    strict_function_checks: true
    gen:
      go:
        package: "db"
        sql_package: "pgx/v4"
        out: "../internal/db"
        emit_interface: true
        emit_exported_queries: true
        emit_result_struct_pointers: true
        emit_params_struct_pointers: true
        emit_enum_valid_method: true
        emit_all_enum_values: true
        output_models_file_name: "entities.go"
        overrides:
          - go_type: "github.com/ericlagergren/decimal.Big"
            db_type: "pg_catalog.numeric"
          - go_type: "github.com/gofrs/uuid.UUID"
            db_type: "uuid"

Database schema

CREATE TYPE "authn_step_limit_status" AS ENUM (
    'nonlimited',
    'limited'
);

CREATE TYPE "authn_step_type" AS ENUM (
    'face_authn'
);

CREATE TABLE "authn_step_limits" (
    "id" uuid PRIMARY KEY,
    "user_id" uuid not null,
    "step_type" authn_step_type not null,
    "failure_attempts" integer not null default 0,
    "status" authn_step_limit_status not null default 'nonlimited',
    "cleared_at" timestamp null,
    "created_at" timestamp not null default current_timestamp,
    "updated_at" timestamp not null default current_timestamp,
    UNIQUE(user_id, step_type)
);

What happened?

Cannot encode []db.AuthnStepType into oid 16449 - []db.AuthnStepType must implement Encoder or be converted to a string

[dilshat]

Having similar issue when using insert with :copyfrom that inserts into enum field: ERROR: COPY from stdin failed: Cannot encode db.NullMyEnum into oid 16952 - db.NullMyEnum must implement Encoder or be converted to a string (SQLSTATE 57014) When is it going to be resolved @kyleconroy ? May be we should upgrade pgx version?

[kyleconroy]

Custom enum arrays work in pgx/v5 as long as you register the custom enum array type: https://github.com/sqlc-dev/sqlc/pull/2510#issuecomment-1723913738