Postgres在Go中的SELECT语句将所有列作为字符串返回(使用pq和database/sql)

huangapple go评论95阅读模式
英文:

Postgres SELECT in Go returns all columns as string (using pq and database/sql)

问题

我正在使用Go语言的database/sql包和pq Postgres驱动程序进行选择操作:

rows, err := db.Query("SELECT (name, age) FROM people WHERE id = 1")

我尝试以正常的方式检索值:

rows.Next()
name := ""
age := 0
err = rows.Scan(&name, &age)

但是我得到了错误:

sql: expected 1 destination arguments in Scan, not 2

sql.(*Rows).Scan的文档说,你可以传递一个字节切片指针,它将被填充为原始结果。所以我尝试了这个:

b := make([]byte, 1024*1024)
rows.Scan(&b)
fmt.Println(string(b))

这样成功了,打印出:

(John,18)

于是我查看了sql.(*Rows).Scan的源代码,发现如果参数的数量与数据库驱动程序返回的结果数量不匹配,就会返回该错误(这是有道理的)。所以,由于某种原因,pq驱动程序似乎将结果集作为单个值返回。为什么会这样呢?

英文:

I'm doing a select in Go using the database/sql package and the pq Postgres driver:

rows, err := db.Query("SELECT (name, age) FROM people WHERE id = 1")

I tried retrieving the values in the normal way:

rows.Next()
name := ""
age := 0
err = rows.Scan(&name, &age)

but I got the error:

sql: expected 1 destination arguments in Scan, not 2

The documentation for sql.(*Rows).Scan says that you can pass a byte slice pointer and it will be filled with the raw results. So I did this:

b := make([]byte, 1024*1024)
rows.Scan(&b)
fmt.Println(string(b))

which succeeded, printing:

(John,18)

So I looked at the source code for sql.(*Rows).Scan, and it turns out that the error is returned if the number of arguments doesn't match the number of results returned by the database driver (which makes sense). So, for some reason, the pq driver seems to be returning the result set as a single value. Why would this be?

答案1

得分: 6

感谢 a_horse_with_no_name 给出正确答案!我在这里发布它以供……后世参考?

在 Postgres 中,使用括号进行 SELECT (a, b) 将返回一个单独的记录,该记录是一个匿名复合类型。去掉括号将分别返回列:SELECT a, b

英文:

Thanks to a_horse_with_no_name for the right answer! I'm posting it here for ... posterity?

In Postgres, doing SELECT (a, b) with parentheses returns a single record, which is an anonymous composite type. Removing the parentheses will return the columns individually: Select a, b.

答案2

得分: 2

当使用带有out参数的function,并且该函数只返回一行时,我遇到了相同的问题。以下是解决方法:

var foo, bar string
err := db.QueryRow("select * from my_function()").Scan(&foo, &bar)

function的形式如下:

create or replace function my_function(
  out first_out varchar,
  out second_out json
) as $$
  -- etc.
$$ language plpgsql;
英文:

When using a function with out parameters which only ever returns one row I was running into the same issue. The following resolved it for me:

<!-- language: go -->

var foo, bar string
err := db.QueryRow(&quot;select * from my_function()&quot;).Scan(&amp;foo, &amp;bar)

The function was of this form:

create or replace function my_function(
  out first_out varchar,
  out second_out json
) as $$
  -- etc.
$$ language plpgsql;

huangapple
  • 本文由 发表于 2014年1月7日 06:42:36
  • 转载请务必保留本文链接:https://go.coder-hub.com/20960688.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定