Postgres returns []uint8 instead of []integer

Question

I'm saving an array of integers into PostgreSQL table, and when trying to retrieve it, I always get []uint8 instead of []int. I tried to user []integer, []bigint, []smallint. nothing works. The array represent a maximum of four items, each one between 1-100, no floating point.

I'm using Go, and I have an object that is an []int, this is the field:

Quantity []int `json:"quantity" db:"quantity"`

I'm trying to fix it but can't find a way to make PostgreSQL to return an []int.

All the other table fields working great. the Quantity field is of type integer[]

This is the insertion query:

"INSERT INTO products (product_id, name, manufacturer, image_url, quantity, amount, notes, store_id, store_name, owner_id) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)", newProduct.UID, newProduct.Name, newProduct.Manufacturer, newProduct.Image, pq.Array(newProduct.Quantity), newProduct.Amount, newProduct.Notes, newProduct.StoreID, newProduct.StoreName, newProduct.OwnerID);

This is how I try and get the data.

err := rows.Scan(&temp.ID, &temp.UID, &temp.Name, &temp.Manufacturer,
        &temp.Image, &temp.Amount, &temp.Notes,
        &temp.StoreID, &temp.OwnerID, &temp.StoreName, &temp.Quantity)

The problem is only with Quantity. If I change my temp object to []uint8 instead of []int I do get the bytes.

Can you show how you make query to db and what packages do you use for that? Also, show types of table's fields that you're trying to fetch. — Grigoriy Mikhalkin, Apr 19 '20 at 18:16
Edited. I'm using "database/sql" and "GitHub.com/lib/pq" packages. — John Doah, Apr 19 '20 at 18:21
Are your trying to insert list of int in a column in table ? — Eklavya, Apr 19 '20 at 18:22
@AbinashGhosh yes, the insert does works, but when Im trying to get the data I get []uint8 instead of []int. — John Doah, Apr 19 '20 at 18:23
You've only provided the insert that you claim works. Where's the go code that doesn't work, the code that tries to retrieve the ints? — mkopriva, Apr 19 '20 at 18:26
@mkopriva Sorry, I added that. the problem is that it does work when I change my object to be []uint8 instead of []int. but I need an array of numbers and not the bytes. Also, can't find a way to cast it on the Scan so I can use it. — John Doah, Apr 19 '20 at 18:29
@mkopriva I use it on the insert so I can insert the array into the database, that's the only way it worked for me. I tried using it on the Scan also, but it does not work. — John Doah, Apr 19 '20 at 18:30
You need to use `pq.Array` in `Scan` as well. Note that `[]uint8` means a byte slice (`byte` is alias for `uint8`), which means raw data, unparsed postgres array. — mkopriva, Apr 19 '20 at 18:31
*"I tried using it on the Scan also, but it does not work."* no, it does work. You must've made a mistake somewhere else. Show the code you've tried with `pq.Array` in `Scan`. — mkopriva, Apr 19 '20 at 18:32
@JohnDoah Then somewhere else is the problem, using `pq.Array(&temp.Quantity)` is the correct way to scan postgres arrays into Go slices. What is the error you got when you used that approach? What is the query you've executed to get that row? etc. — mkopriva, Apr 19 '20 at 18:34
@mkopriva the query is just a select query, nothing special. — John Doah, Apr 19 '20 at 18:37
@JohnDoah but that "nothing special" is not working for you and that makes it special, special in a bad way. I mean if you don't want to share the code that's not working as you expect that's fine by me, but you're just making it harder than necessary for us to help you. — mkopriva, Apr 19 '20 at 18:42

Eklavya · Accepted Answer · 2020-04-20T15:19:32.073

5

Use pq.Array(&temp.Quantity)

The way you store, you have to retrieve also that way.

err := rows.Scan(&temp.ID, &temp.UID, &temp.Name, &temp.Manufacturer,
        &temp.Image, &temp.Amount, &temp.Notes,
        &temp.StoreID, &temp.OwnerID, &temp.StoreName, pq.Array(&temp.Quantity))

And you have to use supported types for pq.Array() also or implement Scanner interface. For integer you can use []sql.NullInt64 or []int64. But it's better to use the same supported type for Scanner and Valuer interface.

Find more details here.

edited Apr 20 '20 at 15:19

answered Apr 19 '20 at 18:32

Eklavya

17,618
4
28
57

Thanks, still trying to get it to work. Is it okay if the column type on the database is integer[]? or should it be something else? – John Doah Apr 19 '20 at 18:38
No. In database it may be byte type because of pq.Array help you to do that – Eklavya Apr 19 '20 at 18:43
Here you can see the implementation of Scaner & Valuer interface for int array. https://github.com/lib/pq/blob/master/array.go#L429 https://github.com/lib/pq/blob/master/array.go#L463 – Eklavya Apr 19 '20 at 18:52
Thanks, I tried that, but I get this error: pq: parsing array element index 0: pq: scanning to int is not implemented; only sql.Scanner – John Doah Apr 20 '20 at 14:41
Thank you very much! I changed to []int64 as you said and it is working. if I want to change to a smaller int size, do I need to implement the scanner? – John Doah Apr 20 '20 at 15:12
Yes, you need to implement Scanner(https://golang.org/pkg/database/sql/#Scanner) interface for `[]int`. – Eklavya Apr 20 '20 at 15:16
Thank you very much! – John Doah Apr 20 '20 at 15:16

Postgres returns []uint8 instead of []integer

1 Answers1