Pgx: v4: How to make custom enum types work?

[MaikuMori]

Assuming I have type like this:

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

How can I make it work?

I've tried to just use string as receiver type for rows.Scan. But I get error along the lines of this:

ERROR: bind message has 9 result formats but query has 10 columns (SQLSTATE 08P01)

I also tried to use custom type like this:

type CustomType string

const (
    A CustomType = "a"
    B CustomType = "b"
    C CustomType = "c"

)

func (s *CustomType) Scan(value interface{}) error {
    asBytes, ok := value.([]byte)
    if !ok {
        return xerrors.New("Scan source is not []byte")
    }
    *s = CustomType (string(asBytes))
    return nil
}

func (s CustomType) Value() (driver.Value, error) {
    values := map[CustomType]interface{}{
        A:      nil,
        B:       nil,
        C:       nil,
    }

    if _, ok := values[s]; !ok {
        return nil, xerrors.New("Wrong value for CustomType")
    }

    return string(s), nil
}

With or without the sql interfaces, I still get the same error.

What I expected is for it to work with type CustomType string out of box without any extra interfaces because it's essentially just string and custom enum values are also strings.

Do I really need to RegisterDataType a custom type for each enum which implements Value interface?

Edit:
I made a smaller query only with the enum field and I got unknown OID error which makes more sense now. Still the question remains - what's the correct way to add custom enum type support?

[jackc]

Should be fixed in jackc/pgtype@4cf1c4481746f931c736085893a4a97ba0056644.

I'll probably tag a new prelease tag for pgx in the next few days but until then you can update the dependency with the following:

go get -u github.com/jackc/pgtype

[ronaldsuwandi]

@jackc I still encountered this issue, already updated to the latest version as you described above. It's actually partially working. The following snippet works

    query := `SELECT roles FROM users LIMIT 1` // role is enum array
    c, err := u.dbPool.Acquire(context.Background())
    if err != nil {
        return user, err
    }
    defer c.Release()

    var roles pgtype.EnumArray
    err = c.QueryRow(context.Background(), query).Scan(
        &roles,
    )

but if I do query with multiple fields it failed

    query := `SELECT id, roles FROM users LIMIT 1` // role is enum array
    c, err := u.dbPool.Acquire(context.Background())
    if err != nil {
        return user, err
    }
    defer c.Release()

    var roles pgtype.EnumArray
    var id string
    err = c.QueryRow(context.Background(), query).Scan(
        &id,
        &roles,
    )

// error: can't scan into dest[1]: unknown oid 24942 cannot be scanned into &{[] [] %!t(pgtype.Status=0)}

and if I use more fields, the error message is different

    query := `SELECT id, name, roles FROM users LIMIT 1` // role is enum array
    c, err := u.dbPool.Acquire(context.Background())
    if err != nil {
        return user, err
    }
    defer c.Release()

    var roles pgtype.EnumArray
    var id string
    var name string
    err = c.QueryRow(context.Background(), query).Scan(
        &id,
        &name,
        &roles,
    )

// error: ERROR: bind message has 2 result formats but query has 3 columns (SQLSTATE 08P01)

[ronaldsuwandi]

To update from my previous post - when I run the code in debugger, turns out if I only specify 1 column (enum array) in the query it scans without any problem but when I have more than 1 columns, during scan, the field descriptor format is changed to 1 (should be 0 that it matches TextFormatCode)

UPDATE: however this works fine if I use the stdlib version

[MaikuMori]

That's exactly what I experienced as well, the error message changes based on the column count.

I haven't check the latest code yet, but I don't use enum arrays.

[jackc]

Do you have a standalone example I can test?

I tested it using the following database struct:

create type color as enum ('red', 'green', 'blue');

create table widgets (
    id int primary key,
    color color not null
);

insert into widgets values (1, 'green');

And this Go code:

package main

import (
    "context"
    "log"

    "github.com/jackc/pgx/v4"
)

func main() {
    conn, err := pgx.Connect(context.Background(), "")
    if err != nil {
        log.Fatalln(err)
    }
    defer conn.Close(context.Background())

    var color string
    err = conn.QueryRow(context.Background(), "select color from widgets where id=1").Scan(&color)
    if err != nil {
        log.Fatalln(err)
    }

    log.Println(color)
}

and I get this output.

2019/08/23 07:13:58 green

[ronaldsuwandi]

Sure, try the following

db struct

CREATE TYPE color AS ENUM (
    'red',
    'green',
    'blue'
    );

CREATE TABLE blah (
    id int,
    note varchar,
    single_color color,
    colors color[]
);

INSERT INTO blah(id,note,single_color,colors) VALUES(1, 'note', 'blue', '{red,green}');

and the go code

package main

import (
    "context"
    "log"

    "github.com/jackc/pgtype"
    "github.com/jackc/pgx/v4"
)

func main() {
    conn, err := pgx.Connect(context.Background(), "")
    if err != nil {
        log.Fatalln(err)
    }
    defer conn.Close(context.Background())

    var color string
    err = conn.QueryRow(context.Background(), "select single_color from blah where id=1").Scan(&color)
    if err != nil {
        log.Fatalln(err)
    }

    // works
    log.Println(color)

    var colors pgtype.EnumArray
    err = conn.QueryRow(context.Background(), "select colors from blah where id=1").Scan(&colors)
    if err != nil {
        log.Fatalln(err)
    }

    // works
    log.Println(colors)

// !!!!!!!! the following doesn't work
    var id int
    err = conn.QueryRow(context.Background(), "select id, single_color from blah where id=1").Scan(&id, &color)
    if err != nil {
        //ERROR->can't scan into dest[1]: unknown oid 25148 in binary format cannot be scanned into %!t(*string=0xc00009c1b0)
        log.Fatalln(err)
    }

    log.Println(id)
    log.Println(color)

    err = conn.QueryRow(context.Background(), "select id, colors from blah where id=1").Scan(&id, &colors)
    if err != nil {
        // ERROR->can't scan into dest[1]: unknown oid 25147 cannot be scanned into &{[{%!t(string=red) %!t(pgtype.Status=2)} {%!t(string=green) %!t(pgtype.Status=2)}] [{%!t(int32=2) %!t(int32=1)}] %!t(pgtype.Status=2)}
        log.Fatalln(err)
    }

    // fail
    log.Println(id)
    log.Println(colors)

    var note string
    err = conn.QueryRow(context.Background(), "select id, note, colors from blah where id=1").Scan(&id, &note, &colors)
    if err != nil {
        // ERROR->ERROR: bind message has 2 result formats but query has 3 columns (SQLSTATE 08P01)
        log.Fatalln(err)
    }

    // fail
    log.Println(id)
    log.Println(colors)
}

When the query has 2 fields, it shows the error that it can't scan into dest, however when the query has 3 fields the error message is different (bind message has 2 results format)

[jackc]

Okay, I think it should be working now. Update again and see if it is resolved.

jack@glados ~/dev/pgx_583 ±master⚡ » go run main.go                                                                                                             1 ↵
Alias tip: gor main.go
2019/08/27 18:53:11 blue
2019/08/27 18:53:11 {[{red 2} {green 2}] [{2 1}] 2}
2019/08/27 18:53:11 1
2019/08/27 18:53:11 blue
2019/08/27 18:53:11 1
2019/08/27 18:53:11 {[{red 2} {green 2}] [{2 1}] 2}
2019/08/27 18:53:11 1
2019/08/27 18:53:11 {[{red 2} {green 2}] [{2 1}] 2}

[ronaldsuwandi]

it works! 👍 thanks!

[gstarikov]

Hi. i got similar error on v4. as hotfix switch to v3. should i prepare standalone example or everything is clear?

can't scan into dest[1]: unknown oid 25068 cannot be scanned into *int

as i see https://github.com/jackc/pgtype/blob/master/pgtype.go#L819
there is no *int case (i'm using golang iota -> pg enum)

[jackc]

While PostgreSQL enums are internally represented as 32-bit integers that information is not directly exposed to queries. You need to treat enums as a string.

[gstarikov]

i have used scanner interface to convert enum(int) to string on the fly. and it looks like it must works, but it doesnt. plz, take a look:
db scruct:

CREATE TYPE color AS ENUM (
    'red',
    'green',
    'blue'
    );

CREATE TABLE blah (
                      id int,
                      single_color color
);

INSERT INTO blah(id,single_color) VALUES(1, 'blue');

go code:

package main

import (
    "context"
    "database/sql/driver"
    "errors"
    "log"

    "github.com/jackc/pgx/v4"
)

type colorType int

const (
    blue colorType = iota
)

func (t colorType) Value() (driver.Value, error) {
    return "blue", nil
}

var ErrInvalid = errors.New("invalid type")

func (t *colorType) Scan(src interface{}) error {
    strVal, ok := src.(string)
    if !ok || strVal != "blue" {
        return ErrInvalid
    }
    *t = blue
    return nil
}

func main() {
    conn, err := pgx.Connect(context.Background(), "")
    if err != nil {
        log.Fatalln(err)
    }
    defer conn.Close(context.Background())

    var color colorType
    err = conn.QueryRow(context.Background(), "select single_color from blah where id=1").Scan(&color)
    if err != nil {
        //ERROR-> can't scan into dest[0]: unknown oid 25247 cannot be scanned into *int
        log.Fatalln(err)
    }

}

[jackc]

Use master of github.com/jackc/pgtype. Your example works there.

Since you are using pgx directly instead of through database/sql you can get better performance by implementing the EncodeText and DecodeText interfaces defined by pgtype.

And beyond that, there is more enum handling on master of pgtype. Checkout EnumType.

[goku321]

I keep getting this ERROR: bind message has 2 result formats but query has 1 columns (SQLSTATE 08P01) When I do sqlx.Connect("pgx", connString). I have no idea why.
My DSN looks like this: host=hostname user=user database=db password=password port=6432 sslmode=require
Everything worked fine when I was directly using pgx.