Querying Postgres composite types from golang

Question

So I am using go-gorp to query postgres and I can't seem to query the composite type inside my table is giving an error. All I want is appropriately nested JSON response. My postgres schema is:

CREATE TYPE PhoneType AS ENUM ('MOBILE', 'HOME', 'WORK');

CREATE TYPE PhoneNumber AS (
  "Number" VARCHAR,
  "Type" PhoneType
);

CREATE TABLE Person (
  "Id" SERIAL PRIMARY KEY NOT NULL,
  "Name" VARCHAR NOT NULL,
  "Email" VARCHAR,
  "Number" PhoneNumber[]
);

Correspondingly in golang, I have

const (
  MOBILE PhoneType = iota
  HOME
  WORK
)

type PhoneNumber struct {
  Number string
  Type   PhoneType
}

type Person struct {
  Id          int
  Name        string
  Email       string
  PhoneNumber        // not sure how to get array of phone numbers here
}

And to query, I am using go-gorp as follows:

dbmap := &gorp.DbMap{Db: db, Dialect: gorp.PostgresDialect{}}
var data []Person
_, err := dbmap.Select(&data, "SELECT * FROM Person")

the result I get has the format

{
  "Id":
  "Name":
  "Email"
  "Number": "{\"(..., ...)\"}"
  "Type": 0
}

what I'm expecting is:

{
  "Id":
  "Name":
  "Email":
  "PhoneNumber": [{
    "Number":
    "Type":
  }]
}

How can I change this nested composite type to the type friendly to my declaration in go?

EDIT: Looks like postres arrays and composite types become strings in go. How can I redesign the schema to achieve similar result?

score -2 · Answer 1 · answered Jan 22 '17 at 12:55

-2

your type declaration is wrong, try this for example:

type Phone struct {
   Number string
   Type   PhoneType
}

type Person struct {
  Id            int
  Name          string
  Email         string
  PhoneNumber   []Phone     
}

answered Jan 22 '17 at 12:55

Elad

664
1
5
14

i tried that already and here's the error i get sql: Scan error on column index 3: unsupported Scan, storing driver.Value type []uint8 into type *[]main.PhoneNumber – tushar Jan 22 '17 at 13:06
Solution does not work, `unsupported Scan, storing driver.Value type []uint8 into type *[]main.PhoneNumber` – Cristóvão Trevisan Aug 28 '20 at 14:05

Querying Postgres composite types from golang

1 Answers1