Scanning into a UUID

[DanielvPhung]

Hello, having issues with the pgtype.UUID. Not sure if I am doing this correctly.

var userId pgtype.UUID
err := tx.QueryRow(context.Background(), sqlQuery).Scan(&userId)
return userId, err

in postgres, my id is defined as:
"id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),

I get this error:

Transaction error: ERROR: invalid byte sequence for encoding "UTF8": 0xa8 (SQLSTATE 22021)

Really stumped here. Am i missing anything? Does the pgtype.uuid not supported the postgres type listed? Not sure how to deal with this. Did some research, but things seem a little outdated when looking at previous resources.

[jackc]

Works for me:

package main

import (
	"context"
	"log"
	"os"

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

func main() {
	ctx := context.Background()

	conn, err := pgx.Connect(ctx, os.Getenv("DATABASE_URL"))
	if err != nil {
		log.Fatal(err)
	}
	defer conn.Close(ctx)

	var userID pgtype.UUID
	err = conn.QueryRow(ctx, "select gen_random_uuid()").Scan(&userID)
	if err != nil {
		log.Fatal(err)
	}

	log.Println(userID.Value())
}

jack@glados ~/dev/pgx_issues/pgx-1918 ±master⚡ » go run .
2024/02/25 08:31:27 647a444c-f378-4e05-82ca-389ffff44b49 <nil>

[DanielvPhung]

Ok, I can get gen_random_uuid()" to work fine and return a pgtype.UUID. But if I am doing an insert statement, with the id as a UUID, with a syntax similar to

insert into users (email) returning id;

with id being defined as this in my database:

"id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),

I run into the error.

To see the full thing:

var userId pgtype.UUID
err := tx.QueryRow(context.Background(),
	`INSERT INTO "Users" 
	("email", "password", "userRoleId") 
	VALUES ($1, $2, $3) RETURNING id`,
	email, hashedPassword, 1).Scan(&userId)
log.Print(userId)
log.Print(userId.Value())

i'll get the error below:

2024/02/25 12:45:36 {[0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0] false}
2024/02/25 12:45:36 <nil> <nil>
2024/02/25 12:45:36 Transaction error: ERROR: invalid byte sequence for encoding "UTF8": 0xfc (SQLSTATE 22021)

[jackc]

As far as pgx is concerned, it doesn't care what the query actually is, just what arguments it takes and what results it returns. i.e. there shouldn't be any different in the queries. I'm fairly certain the problem is somewhere else in your application code.

See if you can duplicate the issue in a completely standalone example. Also, see if the insert works in psql. I would only expect to see that PostgreSQL error with an invalid argument ... for example, if hashPassword is a []byte with raw binary values and you are inserting it into a PostgreSQL text column.

[DanielvPhung]

Welp you fixed it. I'm an idiot, my implementation was incorrect.

Thanks for your help!