Using go-pg to retrieve virtual columns from Postgres

user_10088259 0
Viewed 1246

I'm using go-pg (https://github.com/go-pg/pg) and this code:

type Book struct {
  id   int
  name string
}

var books []Book
err := db.Model(&books).Select()

and everything works good but I need to add a "virtual" column like this:

concat ('info:', 'id:', id, '...') AS info

and I tried to use:

query.ColumnExpr("concat ('info:', 'id:', id, '...') AS info")

but:

  1. go-pg complains with: error="pg: can't find column=info in model=Book (try discard_unknown_columns)"

  2. go-pg doesn't include anymore columns id and name in query: concat... ONLY!

I can understand that because now go-pg doesn't know how to bind data, but I really need that string which I can retrieve from DB only.

Is there a way?

Can I use a custom type like this below?

type CustomBook struct {
  Info string
  Book
}

Does this make sense?

go-pg go postgresql orm database
1 Answers

this approach could work for you:

type Book struct {
  ID   int
  Name string
  Info string `pg:"-"`
}

...
db.Model(&books).ColumnExpr("book.*").ColumnExpr("CONCAT('id:', id, 'name:', name) AS info").Select()

pg:"-" ignores the struct field and it is not created nor it produces any errors

this ignored column is documented here: https://pg.uptrace.dev/models/

another approach, depending on your requirements could be like this:

var r []struct {
  Name string
  Info string
}

db.Model((*Book)(nil)).Column("name").ColumnExpr("CONCAT('id:', id, 'name:', name) AS info").Select(&r)

this second one is documented here: https://pg.uptrace.dev/queries/

user_77479600
Related