Support Postgresql array types

[damianr99]

Is it possible to support array types?
http://www.postgresql.org/docs/9.2/static/arrays.html

From a cursory look at encode.go, both encode() and decode() functions would need to be extended to take or return a slice of any of the current types they handle. Perhaps other parts need changing as well?

Do you think it is feasible to extend this library to support arrays, or would I be better to use the cgo wrapper around libpq for such native/non-standard SQL support? (though ARRAY is actually part of SQL99 standard, so maybe other DBs that don't support it should be called non-std :-)

Ideally, I'd like to do something like:

db.Exec("INSERT INTO t (k, v) VALUES ($1, $2)", "key", []string{"value1", "value2"})

Thanks,

[bmizerany]

Most likely. Take a look at database/sql/driver.Valuer. It should be
straight forward for input and output. I would love to see a patch if you
get it working.

On Mon, Oct 22, 2012 at 11:49 PM, github-dr notifications@github.comwrote:

Is it possible to support array types?
http://www.postgresql.org/docs/9.2/static/arrays.html

From a cursory look at encode.go, both encode() and decode() functions
would need to be extended to take or return a slice of any of the current
types they handle. Perhaps other parts need changing as well?

Do you think it is feasible to extend this library to support arrays, or
would I be better to use the cgo wrapper around libpq for such
native/non-standard SQL support? (though ARRAY is actually part of SQL99
standard, so maybe other DBs that don't support it should be called non-std
:-)

Ideally, I'd like to do something like:

db.Exec("INSERT INTO t (k, v) VALUES ($1, $2)", "key", []string{"value1",
"value2"})

Thanks,

—
Reply to this email directly or view it on GitHubhttps://github.com/bmizerany/pq/issues/49.

[fdr]

I'm a bit leery of automagical array serialization but not outright opposed, because I'd need to see a more detailed proposal about how to handle some corner cases with arrays. As-is arrays can be constructed as a string and submitted, but that's an inconvenient answer that pleases nobody and is probably begging for half-baked implementations that will cause SQL injection vulnerabilities.

One source to look at is libpqtypes to see how they handle this.

Here are some corner cases:

• User defined types
• Multi-dimensional arrays
• Non-rectangular arrays (not supported in the SQL standard or Postgres, but Go doesn't know that)
• Nested composite types
• Arrays contained in a composite type
• NULL members of arrays (consider an array of ints in Go, which cannot be nilled.)

Another possibility is to limit the scope of the patch to handle common, convenient cases (like textarray && $1 where $1 is a []string), e.g. single dimensional arrays.

These questions are probably answerable with a bit of thought, but as adding composite/recursive type support is a pretty major feature expansion, it needs some careful thinking.

[fdr]

Related to this, I have introduced the postgres type as part of argument to encode. This is presumed to be an oid, and so supporting extensibility in handling those has a more obvious place to be inserted.

6235e1b

[blakesmith]

My use case for this feature is a 1-dimensional string array column. Do you still feel its reasonable for a patch to support this simple case? I'm a little confused how #72 relates to this enhancement...

[fdr]

#72 mentioned this issue because one of the proposed solutions I gave to someone's problem involved a data type with a dynamic OID, e.g. user-defined types or any type installed after the catalog bootstrapping phase, e.g. hstore.

I think "free" single dimensional array support for a few common types is probably not a bad idea, but I wouldn't want to impede support for multidimensional arrays or arrays of custom data types. Want to try writing a patch?

[fdr]

I am also slightly concerned about support for NULLity, which appears even in the single-dimensional case.

For example, you want to use and/or receive []strings for PostgreSQL's text[]. However, string is non-nillable in Go, so what would a NULL returned from Postgres in the midst of an array reasonably map to? "" is one answer, but is obviously indistinguishable from an empty string.

[fdr]

(Peeking at database/sql there seems to be NullString, NullBool etc. types, so maybe that can help...)

[vmihailenco]

Did anyone manage to get it working? I am interested in simplest cases like non-nullable []string and []int slices.

[vmihailenco]

I was able to add support for the integer[] array: https://github.com/vmihailenco/pq/commit/8c1d48625460cbe125f40ae4e66184de7e060456 . But I can't find any way to support following query:

db.Exec("INSERT INTO temp VALUES ($1)", []int32{1, 2, 3})

because database/sql unconditionally uses DefaultParameterConverter. Any hints?

[vmihailenco]

I had to remove Execer interface from Connection: https://github.com/vmihailenco/pq/commit/ad5d63de9280027e40d0e5003a6cd722a16b1823 . I am still interested what is right way to do it.

[chrisfarms]

#86 is probably related. Sorry should have probably added it to this issue.

[chrisfarms]

Continuing my quest for a comprehensive arrays/composite/hstore solution with pq, I've started a package for a generic Value type with the aim that it will be a type that can handle any pgtype by configuring it at runtime.

pqutil

Not 100% sure if this is the way to go (due to some of the lost type-checking), but maybe rather than having pq work directly with native slices, it could include a helper system like this for working with the type system?

[fdr]

I am totally open to adding a hook to the encode/decode system so type parsers can do a Real Good Job.

[damianr99]

As a basic, but very useful starting point, is there any way to support the IN operator?, e.g. something like:
SELECT k FROM t where u IN ($1), []int{1,2,3}
Is there any workaround today that I can use to get something like this? The number of rows to select is variable, so IN ($1,$2,$3) isn't an option. That would be super-helpful, thanks.

[westmark]

+1 for support of the IN clause/operator. Would be really helpful!

[fdr]

Yeah, I need to do some work on the codec system, in particular making sure it doesn't break Go 1.0 (as it claims Go 1.1 needs). The main issue right now is cross-version testing (I am trying to support 1.0.2, which is seen in Debian, 1.0.3, and 1.1). I (and nobody else, either) just haven't gotten around to making that work well, so progress is slower than it needs to be.

Part of this is also because if I push something bad to master, the entire world breaks.

[westmark]

I have a workaround in place where I construct the SQL string by hand, so I'm in no way blocked by this. Take the time you need :-) Quality for the win.

[fdr]

You can also help ;) but that's pretty much the reason why so many pull requests are languishing: some are from Time.parse bugs that affect 1.0.2 as seen in Debian and others are submitted with Go 1.1 features.

For example, as-is it took a lot of laboring from @cwds to get the Windows support for user-name defaulting fixed up, basically on the basis of the assurance that he'd try it out and complain if/when it breaks or needs maintenance in the immediate time frame. But now it's in.

[chakrit]

Any ETA for this? I am building an application that makes heavy use of the IN operator. Not making demand or anything but just checking to see how should I get around this for the time being.

[johto]

I read through the discussion here, and I'm not really excited about adding more reasons to do two round-trips to the server for every single query. I'm throwing around some ideas for getting rid of the need we currently have for it in #209, but if we had to also look at the types of the input parameters to know whether to support passing in a slice or not, things suddenly get very very difficult.

I think a better way would be to make the user explicitly specify that he wants to pass in an array:

values := []string{"foo", "bar"}
err := db.Query(`SELECT * FROM foo WHERE bar = ANY($1)`, pq.Array(values))

and the magic behind Array() would handle serializing the slice into the array format PG is expecting. However, we'd have to still encode() all individual elements of the slice before throwing them into the array input format, and Go's lack of generics really fight us there.

[ins429]

Can someone share a workaround example snippet for this?

[ptman]

NULLable columns can be represented as pointers in Go. E.g. []*time.Time for array of nullable timestamps

[kevinburke]

Should this issue be closed now? It looks like #302 adds support for this, and got merged.

[kevinburke]

Sorry - the commit merged in #302 seems to be a red herring, this is still unsupported.

[freeformz]

I think this can be closed since #466 was merged. Please re-open this issue if I am wrong.