Running :copyfrom with a uuid causes ERROR: incorrect binary data format (SQLSTATE 22P03) with PGX

Version

1.12.0

What happened?

A bug happened!

Relevant log output

ERROR: incorrect binary data format (SQLSTATE 22P03)

Database schema

CREATE TABLE IF NOT EXISTS ad_check_results
(
    ad_check_results_id   uuid      DEFAULT gen_random_uuid(),
    query                 varchar(150) NOT NULL CHECK (query != ''),
    executed_at           TIMESTAMP DEFAULT now(),
    location              varchar(150) NOT NULL CHECK (location != ''),
    location_type         varchar(150),
    is_top                boolean      NOT NULL,
    position              int          NOT NULL,
    number_of_top_results int,
    total_number_of_ads   int,
    burst_run_number      int,
    headline              varchar(200) NOT NULL,
    description           varchar(300) NOT NULL,
    url                   TEXT,
    PRIMARY KEY (ad_check_results_id),
    ad_check_parent_id    uuid,
    CONSTRAINT fk_ad_check FOREIGN KEY (ad_check_parent_id) REFERENCES ad_check (ad_check_id) ON DELETE SET NULL
)

SQL queries

-- name: BulkInsertAdResults :copyfrom
INSERT INTO ad_check_results (query, location, location_type, is_top, position,
                              number_of_top_results, total_number_of_ads, headline, description, url,
                              ad_check_parent_id)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11);

Configuration

version: 1
packages:
  - name: db_access
    path: "ad_intelligence_project/db_access"
    queries: ["./ad_intelligence_project/biz_logic/organization/org-sql", "./ad_intelligence_project/biz_logic/ad_intelligence/sql"]
    schema: "./go_common/db/a_db/migrations"
    sql_package: pgx/v4
    emit_json_tags: true
overrides:
  - column: "ad_check.country_to_check"
    go_type: "database/sql.NullString"
  - column: "ad_check.utc_times_of_checks"
    go_type: "github.com/lib/pq.StringArray"

Playground URL

https://play.sqlc.dev/p/2d26c7887908446358ff23c10defa67e2a2d3a7c4f34e2b1a0e686560420659e

What operating system are you using?

Windows

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

Updating my code from:

func (r iteratorForBulkInsertAdResults) Values() ([]interface{}, error) {
	return []interface{}{
		r.rows[0].Query,
		r.rows[0].Location,
		r.rows[0].LocationType,
		r.rows[0].IsTop,
		r.rows[0].Position,
		r.rows[0].NumberOfTopResults,
		r.rows[0].TotalNumberOfAds,
		r.rows[0].Headline,
		r.rows[0].Description,
		r.rows[0].Url,
		r.rows[0].AdCheckParentID,
	}, nil
}

to:

func (r iteratorForBulkInsertAdResults) Values() ([]interface{}, error) {
	asBinary, err := r.rows[0].AdCheckParentID.MarshalBinary()
	if err != nil{
		return nil, err
	}
	return []interface{}{
		r.rows[0].Query,
		r.rows[0].Location,
		r.rows[0].LocationType,
		r.rows[0].IsTop,
		r.rows[0].Position,
		r.rows[0].NumberOfTopResults,
		r.rows[0].TotalNumberOfAds,
		r.rows[0].Headline,
		r.rows[0].Description,
		r.rows[0].Url,
		asBinary,
	}, nil
}

Worked :)

I have encountered this problem too. I do not think this is a bug. But it is nice to write some documents to warn people who want to COPY UUID type.

The reason for this problem is neither google/uuid nor gofrs/uuid implements the BinaryEncoder interface (link) which needs by the pgx to encode your data during COPY.

A quick solution is to add overrides settings to sqlc.yaml to force using UUID type from github.com/jackc/pgtype

For example

version: "1"
project:
    id: ""
packages: 
  - path: "***"
    name: "document"
    engine: "postgresql"
    schema: "***"
    sql_package: "pgx/v4"
    queries: "***"
    overrides:
    - go_type: "github.com/jackc/pgtype.UUID"
      db_type: "uuid"
    - go_type: "github.com/jackc/pgtype.UUID"
      db_type: "uuid"
      nullable: true

I'm running into this too with the following settings:

go version go1.19.4 darwin/amd64
sqlc version v1.17.0
pgx v4
query type :copyfrom
UUID type "github.com/google/uuid"

I noticed an extra detail here which is that the failure only happens for me when a NullUUID is present in the copyfrom query as one of the data rows to insert. Normal UUID behaves fine.

This is consistent with the OP's case it seems since ad_check_parent_id is also nullable there based on the posted schema and that's why the manual intervention hack of adding MarshalBinary() explicitly works.

Is there a better workaround here than overriding with the pgtype as suggested? We have far too many locations where we've been using Google's UUID to make this an easy swap by doing so and that just feels way too heavy-handed as a workaround. We can't afford to wait for pgx v5 to settle either (not to mention the cost of switching over to that).

Is the next best option to change the copyfrom query to a transacted loop of single inserts? I spent some time trying to get a custom type to work but it seemed to suffer the same problem which makes sense since it still doesn't really affect how the copyfrom code is being generated or used.

I believe this has been fixed in pgx/v5. Could you upgrade to v5 and let me know if you're still seeing the issue?

@kyleconroy

@kyleconroy , got some time to work on the upgrade today and this does appear to be fixed now in v5, thank you very much sir!

For anyone else migrating from v4, these were super helpful resources:

Navigation Menu

Repository navigation

Running :copyfrom with a uuid causes ERROR: incorrect binary data format (SQLSTATE 22P03) with PGX #1507

Version

What happened?

Relevant log output

Database schema

SQL queries

Configuration

Playground URL

What operating system are you using?

What database engines are you using?

What type of code are you generating?

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Running :copyfrom with a uuid causes ERROR: incorrect binary data format (SQLSTATE 22P03) with PGX #1507

Description

Version

What happened?

Relevant log output

Database schema

SQL queries

Configuration

Playground URL

What operating system are you using?

What database engines are you using?

What type of code are you generating?

Activity

austincollinpena commented on Mar 22, 2022

CNLHC commented on Sep 7, 2022

sudotliu commented on Feb 16, 2023

kyleconroy commented on Sep 22, 2023

sudotliu commented on Sep 27, 2023

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions