How to write Scan() for custom type in PostgreSQL column in Go

Question

So I have this table Schema that looks like the following

CREATE TABLE artists (
  id SERIAL PRIMARY KEY,
  foo_user_id TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  time_span TIME_SPAN NOT NULL,
  items artist [] NOT NULL
);

In addition, I also have the custom type created in PostgreSQL that is defined as follows

CREATE TYPE artist AS (
  artist_name TEXT,
  foo_artist_id TEXT,
  artist_image_url TEXT,
  artist_rank int
);

I am trying to query all rows that have the "foo_user_id" equal to what I pass into the function. Here is the sample code.

func GetHistoricalTopArtists(foo_user_id string) ([]TopArtists, error) {
// connect to DB etc..

// create prepared statement
stmtStr := `SELECT * FROM artists WHERE foo_user_id=$1`

// error check...

// iterate through all rows to get an array of []TopArtists
defer rows.Close()
    for rows.Next() {
        topArtist := new(TopArtists)
        err := rows.Scan(&topArtist.Id, &topArtist.FooUserId, &topArtist.CreatedAt, &topArtist.TimeSpan, &topArtist.Artists)
        if err != nil {
            log.Fatalf("Something went wrong %v", err)
        }
        topArtists = append(topArtists, *topArtist)
    }
}

To represent this data in Go I created the following structs

// Represents a row
type TopArtists struct {
    Id        int64    `json:"id" db:"id"`
    FooUserId string   `json:"foo_user_id" db:"foo_user_id"`
    CreatedAt string   `json:"created_at" db:"created_at"`
    TimeSpan  string   `json:"time_span" db:"time_span"`
    Artists   []Artist `json:"items" db:"items"`
}

// Represents the artist column
type Artist struct {
    ArtistName      string `json:"artist_name"`
    ArtistId        string `json:"foo_artist_id"`
    ArtistImageURL  string `json:"artist_image_url"`
    ArtistRank      int    `json:"artist_rank"`
}

When I call the function that does the query (the one I described above). I get the following error.

Scan error on column index 4, name "items": unsupported Scan, storing driver.Value type []uint8 into type *[]database.Artist.

I have a Value() function, but I am unsure how to implement a Scan() function for the array of the custom struct I have made.

Here is my Value() function, I have attempted to read documentation and similar posts on scanning arrays of primitive types (strings, int, etc) but I could not apply the logic to custom PostgreSQL types.

func (a Artist) Value() (driver.Value, error) {
    s := fmt.Sprintf("(%s, %s, %s, %d)",
        a.ArtistName,
        a.FooArtistId,
        a.ArtistImageURL,
        a.ArtistRank)
    return []byte(s), nil
}

Note that the field type you want to store/read is []Artist, therefore declaring the Value/Scan methods on Artist will not help you in any way. You need to declare a slice type, e.g. type ArtistSlice []Artist, use that as the field's type, and implement the Value/Scan methods on that. — mkopriva
– mkopriva, Commented Oct 27, 2021 at 19:40

kozmo · Accepted Answer · 2021-10-27 21:09:56Z

5

@mkopriva - ...You need to declare a slice type, e.g. type ArtistSlice []Artist, use that as the field's type, and implement the Value/Scan methods on that.

Created custom Composite Types Artist in Postgresq has a strict struct as

{(david,38,url,1),(david2,2,"url 2",2)}

then you have to implement Value/Scan method with custom marshal/unmarshal algorithm

For example

type Artists []Artist

func (a *Artists) Scan(value interface{}) error {
    source, ok := value.(string) // input example 👉🏻 {"(david,38,url,1)","(david2,2,\"url 2\",2)"}
    if !ok {
        return errors.New("incompatible type")
    }
    
    var res Artists
    artists := strings.Split(source, "\",\"")
    for _, artist := range artists {
        for _, old := range []string{"\\\"","\"","{", "}","(",")"} {
            artist = strings.ReplaceAll(artist, old, "")
        }
        artistRawData := strings.Split(artist, ",")
        i, err := strconv.Atoi(artistRawData[1])
        if err != nil {
            return fmt.Errorf("parce ArtistRank raw data (%s) in %d iteration error: %v", artist, i, err)
        }
        res = append(res, Artist{
            ArtistName:     artistRawData[0],
            ArtistId:       artistRawData[1],
            ArtistImageURL: artistRawData[2],
            ArtistRank:     i,
        })
    }
    *a = res
    return nil
}

edited Oct 27, 2021 at 21:09

answered Oct 27, 2021 at 21:03

kozmo

4,5945 gold badges34 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

PersonWhoLovesLearning Over a year ago

This worked wonderfully. The only problem I came across was I had to modify source, ok := value.(string) to be bytes, ok := value.([]byte) as it was not properly converting it into a string. I then had to convert the array of bytes into a string by doing source := string(bytes). Just in case anyone comes into the same problem that I did.

Marwan Over a year ago

Thank you very much for your explanation and for the "Created" link you posted

zcapital · Accepted Answer · 2024-07-13 22:53:27Z

Just to add up to what kozmo wrote, trimming and splitting bytes instead of strings should be more efficient:

func (a *Artists) Scan(value interface{}) error {
    arrayBytes, ok := value.([]byte) // input example 👉🏻 {"(david,38,url,1)","(david2,2,\"url 2\",2)"}
    if !ok {
        return errors.New("could not convert value to []byte")
    }
    arrayBytes = arrayBytes[2 : len(arrayBytes)-2] // trim {" and "}
    splitArrayBytes := bytes.Split(arrayBytes, []byte{'"', ',', '"'})

    var res Artists
    for _, artistBytes := range splitArrayBytes {
        artistBytes = artistBytes[1 : len(artistBytes)-1] // trim ( and )
        splitArtistBytes := bytes.Split(artistBytes, []byte{','})

        artistRank, err := strconv.Atoi(string(splitArtistBytes[3]))
        if err != nil {
            return errors.Wrap(err, "parse Artist.Rank field")
        }

        res = append(res, Artist{
            ArtistName:     string(splitArtistBytes[0]),
            ArtistId:       string(splitArtistBytes[1]),
            ArtistImageURL: string(splitArtistBytes[2]),
            ArtistRank:     artistRank,
        })
    }

    *a = res
    return nil
}

Collectives™ on Stack Overflow

How to write Scan() for custom type in PostgreSQL column in Go

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related