copy from doesn't work with UUIDs

[matthewmueller]

Wasn't able to get uuid's working. Doesn't seem to work as a UUID, a string or a slice of bytes.

Here's the error I'm seeing:

ERROR: incorrect binary data format (SQLSTATE 22P03)

I took a peak around to see if I could get it working, but it seems like COPY IN needs very specific values. I think there's a mismatch between the postgres uuid type and the encoded uuid string.

Here's the code I'm using:

// ...
events := [][]interface{}{}
for _, ti := range times {
	events = append(events, []interface{}{uuid.NewV4(), ti})
}

// bulk insert
_, err = tx.CopyFrom(pgx.Identifier{"events"}, []string{"task", "time"}, pgx.CopyFromRows(events))
if err != nil {
	log.WithError(err).Error("Unable to copy in events")
	return
}

tx.Commit()

FWIW, I was able to get this working with pq, so there might be something to pull from there.

[matthewmueller]

Looking into this a bit more, it appears that pgx is using the binary format, while pq is using the text format. And this seems to be the underlying issue:

It is somewhat faster than the text and CSV formats, but a binary-format file is less portable across machine architectures and PostgreSQL versions. Also, the binary format is very data type specific; for example it will not work to output binary data from a smallint column and read it into an integer column, even though that would work fine in text format.

ref: https://www.postgresql.org/docs/9.2/static/sql-copy.html

[kelseyfrancis]

What I've done to solve this problem is define types that implement PgxScanner and Encoder and then convert uuid.UUID or just [16]byte to this custom pgx-specific UUID type before querying/scanning.

Example usage:

var uuidIn [16]byte = // ...
var uuidOut [16]byte
err := tx.QueryRow(sql, UUID(uuidIn)).Scan((*UUID)(&uuidOut))

The UUID scanner/encoder code (ignore the UUIDArray stuff if you don't need it to support the UUID[] postgres type):

import (
	"encoding/hex"
	"fmt"
	"bytes"

	"github.com/jackc/pgx"
)

// UUID is a 16-byte array that is a pgx.PgxScanner and pgx.Encoder.
type UUID [16]byte

const uuidArrayOid = 2951

func init() {
	pgx.DefaultTypeFormats["uuid"] = pgx.BinaryFormatCode
	pgx.DefaultTypeFormats["_uuid"] = pgx.BinaryFormatCode
}

// ScanPgx implements pgx.PgxScanner.
func (u *UUID) ScanPgx(vr *pgx.ValueReader) error {
	if vr.Type().DataType != pgx.UuidOid {
		return pgx.SerializationError(fmt.Sprintf("UUID.Scan cannot decode OID %d", vr.Type().DataType))
	}

	if vr.Type().FormatCode != pgx.BinaryFormatCode {
		return pgx.ProtocolError(fmt.Sprintf("Unknown field description format code: %v", vr.Type().FormatCode))
	}

	if vr.Len() == -1 {
		return pgx.ProtocolError("Cannot decode null into *UUID; decode into **UUID instead")
	}

	if vr.Len() != 16 {
		return pgx.SerializationError(fmt.Sprintf("Received an invalid size for a UUID: %d", vr.Len()))
	}

	copy(u[:], vr.ReadBytes(vr.Len()))
	return vr.Err()
}

// Encode implements pgx.Encoder.
func (u UUID) Encode(w *pgx.WriteBuf, oid pgx.Oid) error {
	if oid != pgx.UuidOid {
		return pgx.SerializationError(fmt.Sprintf("UUID.Encode cannot encode into OID %d", oid))
	}

	w.WriteInt32(16)
	w.WriteBytes(u[:])
	return nil
}

// FormatCode implements pgx.Encoder.
func (u UUID) FormatCode() int16 {
	return pgx.BinaryFormatCode
}

func (u UUID) String() string {
	return hex.EncodeToString(u[:])
}

// Less returns whether this UUID is less than the given other UUID.
func (u UUID) Less(other UUID) bool {
	return bytes.Compare(u[:], other[:]) == -1
}

// UUIDArray is a slice of UUID that is a PgxScanner and Encoder.
type UUIDArray []UUID

// ScanPgx implements pgx.PgxScanner.
func (ua *UUIDArray) ScanPgx(vr *pgx.ValueReader) error {
	if vr.Type().DataType != uuidArrayOid {
		return pgx.SerializationError(fmt.Sprintf("UUID.Scan cannot decode OID %d", vr.Type().DataType))
	}

	if vr.Type().FormatCode != pgx.BinaryFormatCode {
		return pgx.ProtocolError(fmt.Sprintf("Unknown field description format code: %v", vr.Type().FormatCode))
	}

	if vr.Len() == -1 {
		*ua = nil
	}

	numEl, err := decode1dArrayHeader(vr)
	if err != nil {
		return err
	}

	a := make([]UUID, numEl)
	for i := 0; i < len(a); i++ {
		elSize := vr.ReadInt32()
		switch elSize {
		case 16:
			copy(a[i][:], vr.ReadBytes(16))
		case -1:
			return pgx.ProtocolError("Cannot decode null UUID element into *UUIDArray")
		default:
			return pgx.ProtocolError(fmt.Sprintf("Received an invalid size for a UUID element: %d", elSize))
		}
	}

	*ua = UUIDArray(a)
	return vr.Err()
}

// Encode implements pgx.Encoder.
func (ua UUIDArray) Encode(w *pgx.WriteBuf, oid pgx.Oid) error {
	if oid != uuidArrayOid {
		return pgx.SerializationError(fmt.Sprintf("UUID.Encode cannot encode into OID %d", oid))
	}

	if ua == nil {
		w.WriteInt32(-1)
		return nil
	}

	encodeArrayHeader(w, pgx.UuidOid, len(ua), 4+16)

	for _, u := range ua {
		w.WriteInt32(16)
		w.WriteBytes(u[:])
	}

	return nil
}

func decode1dArrayHeader(vr *pgx.ValueReader) (length int32, err error) {
	numDims := vr.ReadInt32()
	if numDims > 1 {
		return 0, pgx.ProtocolError(fmt.Sprintf("Expected array to have 0 or 1 dimension, but it had %v", numDims))
	}

	vr.ReadInt32() // 0 if no nulls / 1 if there is one or more nulls -- but we don't care
	vr.ReadInt32() // element oid

	if numDims == 0 {
		return 0, nil
	}

	length = vr.ReadInt32()

	idxFirstElem := vr.ReadInt32()
	if idxFirstElem != 1 {
		return 0, pgx.ProtocolError(fmt.Sprintf("Expected array's first element to start a index 1, but it is %d", idxFirstElem))
	}

	return length, nil
}

func encodeArrayHeader(w *pgx.WriteBuf, oid, length, sizePerItem int) {
	w.WriteInt32(int32(20 + length*sizePerItem))
	w.WriteInt32(1)             // number of dimensions
	w.WriteInt32(0)             // no nulls
	w.WriteInt32(int32(oid))    // type of elements
	w.WriteInt32(int32(length)) // number of elements
	w.WriteInt32(1)             // index of first element
}

// FormatCode implements pgx.Encoder.
func (ua UUIDArray) FormatCode() int16 {
	return pgx.BinaryFormatCode
}

func (ua UUIDArray) String() string {
	return fmt.Sprint([]UUID(ua))
}

[matthewmueller]

@kelseyfrancis thanks so much! I was really stuck and your code helped unstick me.

While trying out your code in my codebase, I stumbled upon bytes, err := uuid.MarshalBinary() in the https://github.com/satori/go.uuid package.

It turns out you can use that directly for the copy from interface:

events := [][]interface{}{}

binary, err := tsk.ID.MarshalBinary()
if err != nil {
	log.WithError(err).Error("Unable to marshal binary")
	return
}

for _, ti := range times {
	events = append(events, []interface{}{ti, binary})
}

// bulk insert
_, err = tx.CopyFrom(pgx.Identifier{"events"}, []string{"time", "task"}, pgx.CopyFromRows(events))
if err != nil {
	log.WithError(err).Error("Unable to copy in events")
	response.Err(w, errors.New("Unable to create the task"))
	return
}

tx.Commit()