Instantly share code, notes, and snippets.

Embed
What would you like to do?
Get get number of rows using sql in golang
package main
import (
"database/sql"
"fmt"
_ "github.com/lib/pq"
)
const (
DB_USER = "ramesh"
DB_PASSWORD = "secret"
DB_NAME = "test_db"
)
func main() {
dbinfo := fmt.Sprintf("user=%s password=%s dbname=%s sslmode=disable",
DB_USER, DB_PASSWORD, DB_NAME)
db, err := sql.Open("postgres", dbinfo)
checkErr(err)
defer db.Close()
rows, err := db.Query("SELECT COUNT(*) as count FROM table_name")
fmt.Println("Total count:",checkCount(rows))
checkErr(err)
}
func checkCount(rows *sql.Rows) (count int) {
for rows.Next() {
err:= rows.Scan(&count)
checkErr(err)
}
return count
}
func checkErr(err error) {
if err != nil {
panic(err)
}
}
@WebStyle

This comment has been minimized.

Show comment
Hide comment
@WebStyle

WebStyle Jul 19, 2016

Thank you!

WebStyle commented Jul 19, 2016

Thank you!

@froind

This comment has been minimized.

Show comment
Hide comment
@froind

froind Jul 23, 2016

not sure how i feel about go at the minute. between this and unmarshalling json its a bit of a pain. also attempt batch inserts into a table. F**k

froind commented Jul 23, 2016

not sure how i feel about go at the minute. between this and unmarshalling json its a bit of a pain. also attempt batch inserts into a table. F**k

@kirillkotliarov

This comment has been minimized.

Show comment
Hide comment
@kirillkotliarov

kirillkotliarov Feb 17, 2017

also u can:
rows := db.QueryRow("you're select count....")
rows.Scan(&count)
return count

And you will not use "for" .

kirillkotliarov commented Feb 17, 2017

also u can:
rows := db.QueryRow("you're select count....")
rows.Scan(&count)
return count

And you will not use "for" .

@agis

This comment has been minimized.

Show comment
Hide comment
@agis

agis Apr 28, 2017

As @gokca suggested, this can be done in a simpler way: https://gist.github.com/agis/7e8cd4c7a20d037c01e663402fcad9d0

agis commented Apr 28, 2017

As @gokca suggested, this can be done in a simpler way: https://gist.github.com/agis/7e8cd4c7a20d037c01e663402fcad9d0

@betasve

This comment has been minimized.

Show comment
Hide comment
@betasve

betasve May 1, 2017

Actually, AFAIK, db.QueryRow("...") is the right way. db.Query() has to be avoided for DB results that return only one row. Also in the case above, it's strongly recommended to use defer rows.Close() right after receiving rows or DB connections will start leaking

betasve commented May 1, 2017

Actually, AFAIK, db.QueryRow("...") is the right way. db.Query() has to be avoided for DB results that return only one row. Also in the case above, it's strongly recommended to use defer rows.Close() right after receiving rows or DB connections will start leaking

@darkwingg

This comment has been minimized.

Show comment
Hide comment
@darkwingg

darkwingg Jun 8, 2017

@betasve What, why does Query have to be avoided for results that return only one row? I have code that is working that queries a lookup table and panics if there is more than one entry. I don't see anything in the docs to suggest that is a problem. Furthermore, the QueryRow documentation says

QueryRow executes a query that is expected to return at most one row. QueryRow always returns a non-nil value. Errors are deferred until Row's Scan method is called.

Expected to return, but not required to return. I read that to say there could be duplicates and there is no way to tell. I think it is just a convenience function.

darkwingg commented Jun 8, 2017

@betasve What, why does Query have to be avoided for results that return only one row? I have code that is working that queries a lookup table and panics if there is more than one entry. I don't see anything in the docs to suggest that is a problem. Furthermore, the QueryRow documentation says

QueryRow executes a query that is expected to return at most one row. QueryRow always returns a non-nil value. Errors are deferred until Row's Scan method is called.

Expected to return, but not required to return. I read that to say there could be duplicates and there is no way to tell. I think it is just a convenience function.

@simongrigorian

This comment has been minimized.

Show comment
Hide comment
@simongrigorian

simongrigorian Aug 24, 2018

var count int
stmt, err = db.Prepare("SELECT COUNT(*) as count FROM YourTable")
if err != nil { log.Fatal(err) }
err = stmt.QueryRow().Scan(&count)
if err != nil { log.Fatal(err) }
log.Println(count)
stmt.Close() // or use defer rows.Close(), idc

Refference:
Go database/sql tutorial

The code here by trkrameshkumar is great though if you need to use the count functions often in various situations. WIll minimize your code, and make it more readable.

Cheers

simongrigorian commented Aug 24, 2018

var count int
stmt, err = db.Prepare("SELECT COUNT(*) as count FROM YourTable")
if err != nil { log.Fatal(err) }
err = stmt.QueryRow().Scan(&count)
if err != nil { log.Fatal(err) }
log.Println(count)
stmt.Close() // or use defer rows.Close(), idc

Refference:
Go database/sql tutorial

The code here by trkrameshkumar is great though if you need to use the count functions often in various situations. WIll minimize your code, and make it more readable.

Cheers

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment