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

Question

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.

You cannot scan 16 columns into a single name string. You need to pass 16 pointer arguments to the Scan call. If you're interested only in the name instead of SELECT * ... do SELECT name ... or whatever the name of the column you want is. — mkopriva
– mkopriva, Commented Sep 6, 2017 at 21:12

SwiftD · Accepted Answer · 2017-09-06 23:28:30Z

4

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)
}

edited Sep 6, 2017 at 23:28

answered Sep 6, 2017 at 21:25

SwiftD

6,1496 gold badges46 silver badges73 bronze badges

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

2 Comments

richard_d_sim Over a year ago

I'm not sure I follow. Could you explain how do go about implementing a holder variable for each column?

SwiftD Over a year ago

Updated with an example

Frankstyl · Accepted Answer · 2019-06-30 05:59:07Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related