Golang + Postgres + sqlc: could not determine data type

sql/queries/foobar.sql:1:1: could not determine data type of parameter $1

My problem was two-fold:

1. I was getting generated names such as Column1, Column2, etc where I wanted proper names.
2. I used sqlc.arg(my_column_name) to get around this, but forgot to re-enumerate the position parameters from $1.

From this perspective, the error message is a bit of a red herring. It's not that the type of parameter $1 is wrong, it's that parameter $1 doesn't exist - period.

The Problem

My original SQL included a type specifier (::ULID) so that the string would be properly cast into a CHAR(26):

-- name: CreateRecord :one
INSERT INTO record (
    "id",
    "data",
    "created_by",
    "created_at"
) VALUES (
    $1::ULID,
    $2,
    $3,
    $4
)
RETURNING *;

That type specifier was causing the generated Go struct to have the name Column1 rather than ID:

type CreateRecordParams struct {
    Column1   interface{}  `json:"column_1"`
    Data      pgtype.JSONB `json:"data"`
    CreatedBy string       `json:"submitted_by"`
    CreatedAt time.Time    `json:"submitted_at"`
}

The Second Problem

You can use sqlc.arg(my_column_name) to force the name of a column, like so:

-- name: CreateRecord :one
INSERT INTO record (
    "id",
    "data",
    "created_by",
    "created_at"
) VALUES (
    sqlc.arg(id)::ULID,
    $2, -- Ooops! Forgot to change to $1, $2, $3...
    $3,
    $4
)
RETURNING *;

In my case I forgot to renumber the parameters, hence the error:

sql/queries/foobar.sql:1:1: could not determine data type of parameter $1

The Solution

Use sqlc.arg(my_column_name) and renumber the params:

-- name: CreateRecord :one
INSERT INTO record (
    "id",
    "data",
    "created_by",
    "created_at"
) VALUES (
    sqlc.arg(id)::ULID,
    $1,
    $2,
    $3
)
RETURNING *;

By AJ ONeal


Did I make your day?
Buy me a coffee  

(you can learn about the bigger picture I'm working towards on my patreon page )

---

---