How to find the rows count returned from db.Query postgres

Question

I am using goLang lib/pq driver and trying to fetch rows from database.

rows, err := db.Query("select id, name from mytable limit 5")

I want to have a if else clause which checks if there are rows in result set and I did this:

if(!rows.Next()){
   log.Printf("no rows returned")
} else {
   log.Printf("rows returned")
}

but this always return me 1 record less and I assume its because of the if clause it skips one record because as soon as I remove if clause I get all records correctly. How can I know the count of rows returned from the select query without executing another query?

Answer 1

When you work with the Rows object, there isn't any helper method that give you the total rows count in one step.

A simple but slower solution is to iterate through all the results using an incremental variable to store the amount of rows:

// error handling omitted
rows, _ := db.Query("SELECT * FROM table")
defer rows.Close()

counter := 0
for rows.Next() {
 // you can even scan+store the result if you need them later
 counter++
}

fmt.Println("we have", counter, "rows")

Otherwise, if your goal is only to "count" the amount of rows, use a more dedicated query with QueryRow

// error handling omitted
var counter int

db.QueryRow("SELECT count(*) FROM table").Scan(&counter)

fmt.Println("we have", counter, "rows")

Answer 2

The sql.Rows type is designed to contain multiple results, and you need to call rows.Scan once per result, and call rows.Next to see whether there's a next result that follows all of the results processed so far.

To quote the example from the documentation for the sql.Rows type:

rows, err := db.Query("SELECT ...")
...
defer rows.Close()
for rows.Next() {
    var id int
    var name string
    err = rows.Scan(&id, &name)
    ...
}
err = rows.Err() // get any error encountered during iteration
...