How do you select the whole table in postgres using golang sql?

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

How do you select the whole table in postgres using golang sql?

问题

我对posgres和Go都比较新手,但是我已经在这个问题上苦苦挣扎了一段时间。我目前正在尝试发送一个查询以从表中检索所有内容。

当我在postgres中尝试使用

SELECT * FROM land_registry_price_paid_uk

时,它会显示所有内容,但是当我使用Query进行相同操作时,我得到了以下错误信息。

sql: 期望在Scan中有16个目标参数,而不是1个

这是我目前的代码。

fmt.Printf("user: %s, password: %s, dbName: %s", user, password, dbName)
connectionString := fmt.Sprintf("user=%s password=%s dbname=%s sslmode=%s", user, password, dbName, "disable")
var err error
a.DB, err = sql.Open("postgres", connectionString)
if err != nil {
	log.Fatal(err)
}
rows, err := a.DB.Query("SELECT ( * ) FROM land_registry_price_paid_uk")
if err != nil {
	log.Fatal(err)
}
defer rows.Close()
println(rows)
for rows.Next() {
	var name string
	if err := rows.Scan(&name); err != nil {
		log.Fatal(err)
	}
	fmt.Printf("this is something: %s\n", name)

非常感谢您的帮助。

英文:

I'm fairly new to posgres and Go, but have been struggling for a while on this. I'm currently trying to send a query to retrieve everything from a table.
When I try to use

SELECT * FROM land_registry_price_paid_uk

within postgres, it shows everything, but when I do the same using Query, I get.

sql: expected 16  destination arguments in Scan, not 1

This is the current code that I have.

fmt.Printf("user: %s, password: %s, dbName: %s", user, password, dbName)
connectionString := fmt.Sprintf("user=%s password=%s dbname=%s sslmode=%s", user, password, dbName, "disable")
var err error
a.DB, err = sql.Open("postgres", connectionString)
if err != nil {
	log.Fatal(err)
}
rows, err := a.DB.Query("SELECT ( * ) FROM land_registry_price_paid_uk")
if err != nil {
	log.Fatal(err)
}
defer rows.Close()
println(rows)
for rows.Next() {
	var name string
	if err := rows.Scan(&name); err != nil {
		log.Fatal(err)
	}
	fmt.Printf("this is something: %s\n", name)

Any help is appreciated.

答案1

得分: 3

看起来你的查询工作正常,你返回了16列的数据,并尝试将它们全部扫描到一个字符串变量中,你需要为每一列提供一个变量:

var name string
// 其他列的变量放在这里

// 然后按照表的顺序将变量作为参数引用到row.Scan中
if err := rows.Scan(&name); err != nil {
    log.Fatal(err)
}

如果你之前没有使用过sql/go,你可能还需要了解处理可空值的特殊类型,因为你可能也会需要它们:

NullString示例

更新:

为了进一步说明,假设你有一个包含以下字段的三列表:

  • id (int)
  • name (string)
  • optional_data (string, 可空)

你可以按照以下方式读取行(未经测试):

var (
    id int 
    name string
    optionalData sql.NullString
)

if err := rows.Scan(&id, &name, &optionalData); err != nil {
    log.Fatal(err)
}
英文:

Looks like your query is working just fine, you are returning 16 columns of data and trying to scan them all into a single string variable, you will need to provide a holder variable for each column:

var name string
// vars to hold other column values go here

// then reference vars in table order as args to row.Scan below
if err := rows.Scan(&name); err != nil {
    log.Fatal(err)
}

if you have not used sql/go before you may also want to look into the special types provided for coping with nullable values as you will likely need these as well:

NullString example

Update:

To further illustrate, say you had a three column table which consisted of the following fields:

  • id (int)
  • name (string)
  • optional_data (string, nullable)

You might read the row as follows (not tested):

var (
    id int 
    name string
    optionalData sql.NullString
)

if err := rows.Scan(&id, &name, &optionalData); err != nil {
    log.Fatal(err)
}

答案2

得分: 1

非常感谢,这对我很有帮助。但是我想再添加一点信息。
嗯,我不知道你使用的是哪个版本的goalng,但我在Windows/386上使用的是1.11.2版本。当我添加了括号后,它就无法正常工作了,但是如果没有括号,它就可以完美运行。

请使用以下代码:
rows, err := a.DB.Query("SELECT * FROM mytable")
而不是:
rows, err := a.DB.Query("SELECT ( * ) FROM mytable")

这是我的示例代码:

rows, err := db.Query("SELECT * FROM words")
if err != nil {
log.Fatal(err)
}
defer rows.Close()
println(rows)
for rows.Next() {
var word, exa1, exa2, mean string
var id int
if err := rows.Scan(&id, &word, &exa1, &exa2, &mean); err != nil {
log.Fatal(err)
}
fmt.Printf("id: %d\n word: %s \n example1: %s \n example2: %s \n meaning: %s\n---\n\n", id, word, exa1, exa2, mean)
}

希望这对你有用。

英文:

Thanks a lot it was useful for me. but I would like to add a little bit more of information.
well I don't know what is your goalng's version but I'm using version 1.11.2 on windows/386 but when I added the parentheses this not worked. but without them it run perfect.

use this:
rows, err := a.DB.Query("SELECT * FROM mytable")
instead of :
rows, err := a.DB.Query("SELECT ( * ) FROM mytable")

and here is my example:

rows, err := db.Query("SELECT * FROM words")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()
	println(rows)
	for rows.Next() {
		var word, exa1, exa2, mean string
		var id int
		if err := rows.Scan(&id, &word, &exa1, &exa2, &mean); err != nil {
			log.Fatal(err)
		}
		fmt.Printf("id: %d\n word: %s \n example1: %s \n example2: %s \n meaning: %s\n---\n\n", id, word, exa1, exa2, mean)
	} 

  I hope this will be useful.

huangapple
  • 本文由 发表于 2017年9月7日 05:10:31
  • 转载请务必保留本文链接:https://go.coder-hub.com/46084388.html
匿名

发表评论

匿名网友

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

确定