使用StructScan将postgresql数组导入struct(Get postgresql array into struct with StructScan)

测试数据：

CREATE TABLE test (id int, data text[]) INSERT INTO test(id, data) VALUES(1, '{a,b,c}')

去代码。 第一个 - 工作得很好的一个：

func main() { db, _ := sqlx.Open("postgres", "user=postgres dbname=test sslmode=disable") var id int var asSlice []string err := db.QueryRowx(`SELECT id, data FROM test WHERE data @> ARRAY['b']`).Scan(&id, pq.Array(&asSlice)) if err != nil { log.Fatal(err) } fmt.Println(id, asSlice) }

按预期得到1 [abc] 。 但在这里我手动将结果分配给变量

现在，使用StructScan到无效的部分

type MyStruct struct { Id int Data []string } func main() { db, _ := sqlx.Open("postgres", "user=postgres dbname=test sslmode=disable") var testStruct MyStruct err := db.QueryRowx(`SELECT id, data FROM test WHERE data @> ARRAY['b']`).StructScan(&testStruct) if err != nil { log.Fatal(err) } fmt.Println(testStruct) } sql: Scan error on column index 1: unsupported Scan, storing driver.Value type []uint8 into type *[]string

我想这意味着sqlx不知道postgresql数组，并且内部不使用pq.Array。

我该怎么办呢？ 也许我做错了什么？ 或者我应该手动应用pq.Array？ 如果是这样 - 怎么样？

Test data:

CREATE TABLE test (id int, data text[]) INSERT INTO test(id, data) VALUES(1, '{a,b,c}')

Go Code. First - one that is working just fine:

func main() { db, _ := sqlx.Open("postgres", "user=postgres dbname=test sslmode=disable") var id int var asSlice []string err := db.QueryRowx(`SELECT id, data FROM test WHERE data @> ARRAY['b']`).Scan(&id, pq.Array(&asSlice)) if err != nil { log.Fatal(err) } fmt.Println(id, asSlice) }

I get 1 [a b c] as expected. But here I manually assign results to the variables

Now, to the part that is not working - using StructScan

type MyStruct struct { Id int Data []string } func main() { db, _ := sqlx.Open("postgres", "user=postgres dbname=test sslmode=disable") var testStruct MyStruct err := db.QueryRowx(`SELECT id, data FROM test WHERE data @> ARRAY['b']`).StructScan(&testStruct) if err != nil { log.Fatal(err) } fmt.Println(testStruct) } sql: Scan error on column index 1: unsupported Scan, storing driver.Value type []uint8 into type *[]string

I guess that means that sqlx does not know about postgresql arrays and does not use pq.Array internally.

What should I do about it? Maybe I am doing something wrong? Or maybe I should apply pq.Array manually? If so - how?

最满意答案

尝试使用[]字符串的pq.StringArray类型

type MyStruct struct { Id int Data pq.StringArray }

Try using pq.StringArray type for []string

type MyStruct struct { Id int Data pq.StringArray }使用StructScan将postgresql数组导入struct(Get postgresql array into struct with StructScan)

测试数据：

CREATE TABLE test (id int, data text[]) INSERT INTO test(id, data) VALUES(1, '{a,b,c}')

去代码。 第一个 - 工作得很好的一个：

func main() { db, _ := sqlx.Open("postgres", "user=postgres dbname=test sslmode=disable") var id int var asSlice []string err := db.QueryRowx(`SELECT id, data FROM test WHERE data @> ARRAY['b']`).Scan(&id, pq.Array(&asSlice)) if err != nil { log.Fatal(err) } fmt.Println(id, asSlice) }

按预期得到1 [abc] 。 但在这里我手动将结果分配给变量

现在，使用StructScan到无效的部分

type MyStruct struct { Id int Data []string } func main() { db, _ := sqlx.Open("postgres", "user=postgres dbname=test sslmode=disable") var testStruct MyStruct err := db.QueryRowx(`SELECT id, data FROM test WHERE data @> ARRAY['b']`).StructScan(&testStruct) if err != nil { log.Fatal(err) } fmt.Println(testStruct) } sql: Scan error on column index 1: unsupported Scan, storing driver.Value type []uint8 into type *[]string

我想这意味着sqlx不知道postgresql数组，并且内部不使用pq.Array。

我该怎么办呢？ 也许我做错了什么？ 或者我应该手动应用pq.Array？ 如果是这样 - 怎么样？

Test data:

CREATE TABLE test (id int, data text[]) INSERT INTO test(id, data) VALUES(1, '{a,b,c}')

Go Code. First - one that is working just fine:

func main() { db, _ := sqlx.Open("postgres", "user=postgres dbname=test sslmode=disable") var id int var asSlice []string err := db.QueryRowx(`SELECT id, data FROM test WHERE data @> ARRAY['b']`).Scan(&id, pq.Array(&asSlice)) if err != nil { log.Fatal(err) } fmt.Println(id, asSlice) }

I get 1 [a b c] as expected. But here I manually assign results to the variables

Now, to the part that is not working - using StructScan

type MyStruct struct { Id int Data []string } func main() { db, _ := sqlx.Open("postgres", "user=postgres dbname=test sslmode=disable") var testStruct MyStruct err := db.QueryRowx(`SELECT id, data FROM test WHERE data @> ARRAY['b']`).StructScan(&testStruct) if err != nil { log.Fatal(err) } fmt.Println(testStruct) } sql: Scan error on column index 1: unsupported Scan, storing driver.Value type []uint8 into type *[]string

I guess that means that sqlx does not know about postgresql arrays and does not use pq.Array internally.

What should I do about it? Maybe I am doing something wrong? Or maybe I should apply pq.Array manually? If so - how?

最满意答案

尝试使用[]字符串的pq.StringArray类型

type MyStruct struct { Id int Data pq.StringArray }

Try using pq.StringArray type for []string

type MyStruct struct { Id int Data pq.StringArray }