"error": "Couldn't create User: ERROR: of relation \"users\" violates not-null constraint

Question

I'm creating rssagg app

this is the error i got

{
  "error": "Couldn't create User: ERROR: null value in column \"id\" of relation \"users\" violates not-null constraint (SQLSTATE 23502)"
}

this is my handler user go function

    package main

import (
    "encoding/json"
    "fmt"
    "log"
    "net/http"
    "time"

    "github.com/google/uuid"
    "github.com/jackc/pgx/v5/pgtype"
    internal "github.com/sakthi-lucia0567/rssagg/internal/database"
)

func (apiCfg *apiConfig) handleCreateUser(w http.ResponseWriter, r *http.Request) {
    type parameters struct {
        Name string `json:"name"`
    }
    decoder := json.NewDecoder(r.Body)

    params := parameters{}

    err := decoder.Decode(&params)
    if err != nil {
        respondWithError(w, 400, fmt.Sprintf("Error parsing JSON: %v", err))
        return
    }

    userUUID := uuid.New()
    // generatedType := pgtype.UUID{Bytes: userUUID[:]}
    generatedType := pgtype.UUID{Bytes: userUUID, Valid: true} // this is the code that chatgpt gave but it's seems it's also not working for me. 
    log.Println("user uuid", userUUID)
    log.Println("user generated type", generatedType)
    user, err := apiCfg.DB.CreateUser(r.Context(), internal.CreateUserParams{
        ID:        generatedType,
        CreatedAt: pgtype.Timestamp{Time: time.Now()},
        UpdatedAt: pgtype.Timestamp{Time: time.Now()},
        Name:      params.Name,
    })
    if err != nil {
        respondWithError(w, 400, fmt.Sprintf("Couldn't create User: %v", err))
        return
    }

    respondWithJSON(w, 200, user)
}

this is my models.go generated by the sqlc

// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.26.0

package internal

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

type User struct {
    ID        pgtype.UUID
    CreatedAt pgtype.Timestamp
    UpdatedAt pgtype.Timestamp
    Name      string
}

user.sql.go

// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.26.0
// source: users.sql

package internal

import (
    "context"

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

const createUser = `-- name: CreateUser :one
INSERT INTO users (id, created_at, updated_at, name)
VALUES ($1, $2, $3, $4)
RETURNING id, created_at, updated_at, name
`

type CreateUserParams struct {
    ID        pgtype.UUID
    CreatedAt pgtype.Timestamp
    UpdatedAt pgtype.Timestamp
    Name      string
}

func (q *Queries) CreateUser(ctx context.Context, arg CreateUserParams) (User, error) {
    row := q.db.QueryRow(ctx, createUser,
        arg.ID,
        arg.CreatedAt,
        arg.UpdatedAt,
        arg.Name,
    )
    var i User
    err := row.Scan(
        &i.ID,
        &i.CreatedAt,
        &i.UpdatedAt,
        &i.Name,
    )
    return i, err
}

this is the log i get while running the server:

2024/04/11 00:56:22 user uuid 18bd07ce-a47d-484a-b2d3-1b1fb8d2bf9e
2024/04/11 00:56:22 user generated type {[24 189 7 206 164 125 72 74 178 211 27 31 184 210 191 158] false}

i'm expecting to creating a user but the uuid is coming as a Bytes

the uuid is generating but i couldn't set them to postgres id

Answer 1

pgtype.UUID is defined as (assuming that you are using PGX v5):

type UUID struct {
    Bytes [16]byte
    Valid bool
}

So when you assign with pgtype.UUID{Bytes: userUUID} the value of Valid will be the default (false; and you can see this in your output). PGX types work in the same way as the sql package Null types (e.g. NullBool); that is if Valid is false then the value will be treated as a NULL when sent to the database.

To fix this use:

generatedType := pgtype.UUID{Bytes: userUUID, Valid: true}

You will need to use a similar approach with pgtype.Timestamp.

Note: This is based on the info you provided (I've made assumptions about what apiCfg.DB.CreateUser does).