CODEWITHSUNDEEP
goprepared-statementpsql
amlwwalker
amlwwalker

Reputation: 3314

Goland PSQL prepared statement always throwing error when updating JSONB value for a key

I'm getting myself confused I think, but I am trying to update a value in a JSON object (jsonb column) of a psql database like so

func updateProfileLessonProgress(userID string, lessonID, currentSlide int, db *sql.DB) error {
    fmt.Println("user Id ", userID, "lesson ID ", lessonID, " current slide ", currentSlide)
    query := `
        UPDATE profiles
        SET lesson_progress = jsonb_set(
            lesson_progress,
            $1::text[],
            to_jsonb($2::int),
            true
        )
        WHERE id = $3
    `

    // Convert lessonID to a JSON path (e.g., '{"5"}')
    path := fmt.Sprintf(`{"%d"}`, lessonID)
    // Execute the query with lessonID as string, currentSlide as int, and userID
    _, err := db.Exec(query, path, currentSlide, userID)
    return err
}

Everything from the print outs seems fine, but there error I get is always the same

pq: bind message supplies 3 parameters, but prepared statement "" requires 1

I have googled alternative approaches to this and have also attempted

func updateProfileLessonProgress(userID string, lessonID, currentSlide int, db *sql.DB) error {
    jsonStr := fmt.Sprintf(`{"%d": %d}`, lessonID, currentSlide)
    query := `
        UPDATE profiles
        SET lesson_progress = lesson_progress || $1::jsonb
        WHERE id = $2
    `
    _, err := db.Exec(query, jsonStr, userID)
    return err
}

but in this case I get the same error just now one less:

pq: bind message supplies 2 parameters, but prepared statement "" requires 1

please can someone help me understand why this won't work? Thanks

Upvotes: 0

Views: 23

Answers (0)

Related Questions