Sqlx: How to insert an ARRAY type?

[eldilibra]

I've a struct like:

type Foo struct {
  //some other fields here
  Things []string `db:"things"`
}

When I run something like this through NamedQuery:

INSERT INTO foos (things) VALUES (:things)

I get the following error message:
"sql: converting Exec argument #3's type: unsupported type []string, a slice"

My PostgreSQL db has that field set to an ARRAY (of type uuid, but that is besides the point)

Are we just not allowed to use ARRAYs under the hood with sqlx?

[eldilibra]

Definitely could be pq, not sqlx... if so, will delete immediately ha.

[jmoiron]

The tipoff here is the error message, which starts with sql: ..., meaning that sqlx under the hood is passing the argument as expected, but the somewhere down the line (the driver I'd imagine) it's not handling that type.

I think the postgres wire format for an array is something like {value,value,value}. There is a pull request open on lib/pq which has a patch (not currently passing tests) to support different types of arrays similar to how time.Time is supported, but I don't know how close it is to being complete/accepted. Until then, you can create your own array type and implement sql.Scanner and driver.Valuer on them:

type pgStringSlice []string

func (p *pgStringSlice) Scan(src interface{}) error { ... }
func (p *pgStringSlice) Value() (driver.Value, error) { ... } 

For integers it should be relatively straight forward. For strings it can get a bit complicated. At my job, we have strings that strictly will not contain commas, which made decoding/encoding simply strings.Split. Your needs may be more sophisticated; certainly the pull request I mentioned has presumably a working implementation of array encoding and decoding for general use, and it weighs in at > 1kloc.

[eldilibra]

Thanks for all of this. I'll either roll my own, like what you've above, or just use a join table _facepalm_. Did some digging in the standard lib, and this is my culprit, in the database/sql package:

// Value is a value that drivers must be able to handle.
// It is either nil or an instance of one of these types:
//
//   int64
//   float64
//   bool
//   []byte
//   string   [*] everywhere except from Rows.Next.
//   time.Time
type Value interface{}

[jmoiron]

Those are the ones drivers _must_ implement. They may implement additional types (the popular postgres and mysql drivers both handle time.Time, for instance)

[eldilibra]

Right; makes sense. Compositions of the above types really should be implementers' responsibilities. Maybe after []string I'll add []RedBlackTree support :dancer:. Thanks for your time.

[eldilibra]

Oh, btw, time.Time is in the above list already.

[jmoiron]

Ah yes, I wonder if that was always there...

Anyway, good luck :) I have been thinking of adding pq array types to sqlx/types for a long time but now that this patch is out there maybe I could do it easily.

[fiatjaf]

For people wandering here, I think I should mention the string_to_array(text, text [, text]) Postgres array function which should make simpler (although a little hackier) to insert arrays.

[gravis]

Are you talking about this PR? https://github.com/lib/pq/pull/302
thanks