Inserting an array into a Postgresql database

Question

I want to be able to write an array of bigints into a table that I am using for history in Go. Unfortunately, I can't and when I do the error sql: converting Exec argument #1's type: unsupported type []int64, a slice is thrown. Here's what I'm doing, edited for brevity:

type Card struct {
    cid int64
}

type Transaction struct {
        tid, cardid int64
        productids []int64
        salepoint int
        cardkey string
}

func logPurchase(card *Card, t *Transaction) {
     _, err := db.Exec("INSERT INTO history VALUES ($1, $2, $3, $4)", rand.Int63(), t.productids, card.cid, t.salepoint);
}

This is the structure of the table that I wish to insert into: tid bigint primary key, productids bigint[] not null, cardid bigint not null, salepoint int

Good catch, not sure how I managed to put that there. That issue doesn't exist within the source code where I am having this problem. Updated the question accordingly. — ShrekTheDinosaur
– ShrekTheDinosaur, Commented Nov 3, 2014 at 4:18
Array types are postgresql-specific. You might need to use a library that's tailored to postgresql, such as: github.com/go-pg/pg — dyoo
– dyoo, Commented Nov 3, 2014 at 5:50
As of this writing, it doesn't appear that the lib/pq library currently handles array types, though there is a feature request: github.com/lib/pq/issues/49 — dyoo
– dyoo, Commented Nov 3, 2014 at 5:52
Recommend updating accepted answer to stackoverflow.com/a/41337887/2662176, after this feature was added. — user3.1415927
– user3.1415927, Commented Apr 23, 2019 at 13:48

kennytm · Accepted Answer · 2016-12-27 01:21:20Z

29

Arrays are supported in github.com/lib/pq since 2016 Aug 6th. OP's statement could be written as:

_, err := db.Exec(
    "INSERT INTO history VALUES ($1, $2, $3, $4)", 
    rand.Int63(), 
    pq.Array(t.productids),   // <------- 
    card.cid, 
    t.salepoint,
)

answered Dec 27, 2016 at 1:21

kennytm

526k110 gold badges1.1k silver badges1k bronze badges

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

Comments

andybalholm · Accepted Answer · 2014-11-03 17:19:51Z

12

Implement database/sql/driver.Valuer with a custom type:

type int64array []int64

func (a int64array) Value() (driver.Value, error) {
    // Format a in PostgreSQL's array input format {1,2,3} and return it as as string or []byte.
}

answered Nov 3, 2014 at 17:19

andybalholm

16.3k4 gold badges42 silver badges43 bronze badges

4 Comments

STE Over a year ago

hi andy, cool answer. How can you implement the opposite though? Any function I need to implement to perform the psql array -> slice ?

andybalholm Over a year ago

For the reverse operation, you need to implement the database/sql.Scanner interface. For that you would need to use a pointer receiver.

STE Over a year ago

It would be useful to add it to your answer for reference, if you want - thanks anyway!

user2563451 Over a year ago

It's already in there you just can use pg.Int64Array type for productids field

Collectives™ on Stack Overflow

Inserting an array into a Postgresql database

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related