Missing destination name id

[mahaben]

I'm trying to get all rows from a table in a postgres database but I get the following error "Missing destination name id" even if the id exists on the model.

Can anyone help?

Thanks,

[jhngrant]

@mahaben can you share a code snippet to look at?

[jhngrant]

@mahaben check this https://github.com/jmoiron/sqlx/issues/234

[mahaben]

I checked the issue #234 but it did not helped me.

Here's my code:

type MachinesModel struct { ID *int json:"id" db:"ID" EntityName string json:"entity_name" db:"ENTITY_NAME" Description zero.String json:"description" db:"DESCRIPTION" }

and my table is as following: CREATE TABLE IF NOT EXISTS MACHINES (ID SERIAL PRIMARY KEY,ENTITY_NAME TEXT ,DESCRIPTION TEXT)

Here's my code to select all rows:

err := Db.Select(&models, "SELECT * from machines")

I'm getting "Missing destination name id".

Note that it works when I use the function "Db.Query" but I want to use Db.Select and I want to understand why it doesn't work.

The same code works with oracle, sqlite, mssql and mysql but not with postgres.

Thanks,

[jhngrant]

@mahaben By convention Postgres uses lower case identifiers unless they are quoted. My guess is Postgres has translated your DDL to:

create table machines
(
    id serial not null
        constraint machines_pkey
            primary key,
    entity_name text,
    description text
);

Postgres is case-sensitive so try updating your struct tags to lower case and give that a try:

type MachinesModel struct {
    ID          *int `json:"id" db:"id"`
    EntityName  string `json:"entity_name" db:"entity_name"`
    Description zero.String `json:"description" db:"description"`
}

https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

[mahaben]

@jhngrant Thank you for your reply. I understand but I have to keep them in upper case because I have 100 models written in the same way...

[mahaben]

Found the solution, now I'm using a new mapper with json tag:

db.Mapper = reflectx.NewMapperFunc("json", strings.ToLower)

Thanks @jhngrant again for your help!

[jhngrant]

@mahaben my misunderstanding. In Postgres you'll have to recreate your table using quoted identifiers:

CREATE TABLE "MACHINES"
(
  "ID"          SERIAL NOT NULL
    CONSTRAINT machines_pkey
    PRIMARY KEY,
  "ENTITY_NAME" TEXT,
  "DESCRIPTION" TEXT
);

[mahaben]

@jhngrant This might be a solution but I don't want to be obliged to use upper case when doing queries in psql cmd. I finally found a solution. I added the following statement when my database is "postgres" and it works perfectly

case "postgres":
        db, err = postgresOpen(dsn)
        db.Mapper = reflectx.NewMapperTagFunc("db",
            nil,
            func(s string) string {
                return strings.ToLower(s)
            },
        )

[gayanhewa]

For anyone else who faces a similar issue, I came across something similar due to a really silly mistake on my end as well.

type user struct {
    ID        uuid.UUID `json:"id,omitempty", db:"id"` // notice the comma in the tags this screwed up everything.
}

I had added a silly comma somehow and it was just invisible to my eye for 1 whole day 🤯

[izolate]

Also hit this issue by using unexported struct identifiers. Silly mistake which seems obvious in retrospect.

 type Foo struct {
-    bar string `db:"bar"`
+    Bar string `db:"bar"`
 }

[d-vignesh]

Facing this issue, if i have an underscore in my sql attribute name, TokenHash string json:"token_hash" sql:"token_hash" -> is giving me an error "missing destination name token_hash" whereas TokenHash string json:"tokenhash" sql:"tokenhash" -> is executing fine without any error.