# Unnest with Reflection in Go

Recently at work I needed to write a utility to sync a PostgreSQL database with another data source. In my attempts to make the synchronization efficient, I came across a very helpful StackOverflow question which explained how to perform bulk insertions and updates. The recommended solution looked something like this:

INSERT INTO mytable (col1, col2, col3)
VALUES (UNNEST(?), UNNEST(?), UNNEST(?))

UPDATE mytable
SET value = data_table.new_value
FROM (
SELECT UNNEST(?) AS key, UNNEST(?) AS new_value
) AS data_table
WHERE mytable.key = data_table.key


In my manual testing, I discovered that types were necessary on the arrays, otherwise Postgres complained that the calls to UNNEST were not unique.

INSERT INTO mytable (col1, col2, col3)
VALUES ($1::text[]), UNNEST($2::text[]), UNNEST(\$3::integer[])


With that it was time to implement the statements programmatically.

The data I wanted to insert into the database had a structure something like this:

type MyData struct{
Column1 string db:"col1"
Column2 string db:"col2"
Column3 int64  db:"col3"
}

var toInsert []MyData


Postgres however does not want a list of structs in those unnest functions. Instead, each unnest must contain a list of values for a single column. Essentially I needed to pivot the data.

The process I am writing will be syncing data for several tables. To avoid writing the same pivot function for several structs, I came up with a generic function that can perform this for any struct.

func unnest(data []interface{}, fields []string) []interface{} {
toReturn := make([]interface{}, 0, len(fields))

// Find the type of each field in the struct,
// and create a reflect.SliceOf for each
reflectedV := reflect.ValueOf(data[0])
for _, fieldName := range fields {
elemType := reflectedV.FieldByName(fieldName).Type()
elemSlice := reflect.MakeSlice(reflect.SliceOf(elemType),
0, len(data))
toReturn = append(toReturn, elemSlice)
}

// Using more reflection, stick the value for each field
// in the appropriate slice
for _, v := range data {
reflectedV := reflect.ValueOf(v)

for i, fieldName := range fields {
val := reflectedV.FieldByName(fieldName)
toReturn[i] = reflect.Append(
toReturn[i].(reflect.Value), val)
}
}

// We don't want to return a slice of reflect.Value
// extract the actual slice from each element and convert to an
// array that pq can interpret.
for i := range toReturn {
elem := toReturn[i].(reflect.Value)
sliceElem := elem.Interface()

//toReturn[i] = pq.Array(sliceElem)
toReturn[i] = sliceElem
}