Need help understanding CopyFrom into simple enum columns

[gurunandan-bhat]

Hi,

I am facing difficulties in understanding pgx.CopyFrom and would sincerely appreciate any help I can get. Let me list my difficulties

1. In a simple and small table with three/four columns, one of which is an enum, everything works fine. I can bulk-insert from a struct. I can bulk-insert with a literal string (a valid enum value) for the column. I do not need to call either LoadType or RegisterType. I do not need to create the type during an active connection and I can create it outside (from psql for example) and everything works like a charm. Here is a working snippet with a literal string delivered via CopyFromSlice:

	type gender string
	type person struct {
		name   string
		gender gender
	}

	bulkData := []person{
		{gender: "male", name: "John"},
		{gender: "undisclosed", name: "Joe"},
		{gender: "female", name: "Jill"},
	}

	var count int64
	if count, err = pgxPool.CopyFrom(
		context.Background(),
		pgx.Identifier{"people"},
		[]string{"gender", "name"},
		pgx.CopyFromSlice(len(bulkData), func(i int) ([]any, error) {
			return []any{"undisclosed", bulkData[i].name}, nil
		}),
	); err != nil {
		log.Fatalf("Error in bulk copy: %s", err)
	}

2. However in the real situation that I need to use CopyFrom, I have a table with 16 columns and one of them is an enum. In this situation nothing seems to be working. I have for example set it up after connecting like this:

	conn, err := pgxPool.Acquire(context.Background())
	if err != nil {
		log.Fatalf("Error acquiring a connection from the pool: %s", err)
	}

	ResponseType, err := conn.Conn().LoadType(context.Background(), "response_type")
	if err != nil {
		log.Fatalf("Error loading enum response_type: %s", err)
	}
	conn.Conn().TypeMap().RegisterType(ResponseType)

I have tried several ways of incorporating the enum value in the source Slice. I have tried a literal string (since it seems to work in the example above). I have tried a literal "'value'::response_type". I have even tried calling Encode to generate a binary buffer like this (the enum value I want to insert is reply) :

buf, err = pgtype.NewMap().Encode(ResponseType.OID, pgx.BinaryFormatCode, []byte("reply"), buf)
		if err != nil {
			log.Fatalf("Error generating binary for enum: %s\n", err)
		}

I have gone through all examples as well as tests to see if I might find an example with an INSERT that I could replicate, but without luck. All examples I could find deal with selects (decoding) but none with inserts (encoding) into an enum column. Perhaps I missed a couple?

Each time I get the same error:

ERROR: COPY from stdin failed: unable to encode []interface {}{"reply"} into binary format for unknown type (OID 17176): cannot find encode plan (SQLSTATE 57014)

or some variation of this depending on what I send into the source Slice. For example:

ERROR: COPY from stdin failed: unable to encode []byte{0x72, 0x65, 0x70, 0x6c, 0x79} into binary format for unknown type (OID 17176): cannot find encode plan (SQLSTATE 57014)

Do I need an encode plan? If so, can you point me to an example of one such? And there is the question of why a literal string in the source works for a table with a few columns but not when the number of columns is bigger.

I am using the latest version of Go (1.20.1) and the latest version of pgx/pgxpool (5.3.0)
Thank you!!

[jackc]

There are two formats to encode values to send to PostgreSQL, text and binary. Normal queries can mix and match the format of parameters. The copy protocol requires all data be in the same format. The reason your enums work for normal queries is it can fall back to the text format. However, pgx's CopyFrom exclusively uses the binary format. That is why your enum fails there.

You have the right idea with LoadType and RegisterType, however, you are running it in the wrong place. It should be run in a AfterConnect hook (https://pkg.go.dev/github.com/jackc/pgx/[email protected]/pgxpool#Config).

[gurunandan-bhat]

I now loaded and registered the type in the AfterConnect hook and bulk-copying enum columns works fine. Thank you :)

Indeed, I expected bulk copy to use binary encoding exclusively, and I am still not sure why it prefers text encoding for small tables. My sample code also uses the copy command as you can see from the first snippet.

Thanks for the AfterConnect suggestion