bmizerany / pq - Detailed error returned from postgres

[bsr]

Hello,

When I do the below insert statement, the sql result returned is 

2012/07/12 20:36:50 INSERT INTO books (id, title, price) VALUES (912312444, 'My title', 20) RETURNING id

2012/07/12 20:36:50 sql: no rows in result set

---

The postgres log gives explicit details

LOG:  execute 1: INSERT INTO books (id, title, price) VALUES (912312444, 'My title', 20) RETURNING id

ERROR:  duplicate key value violates unique constraint "books_pkey"

DETAIL:  Key (id)=(912312444) already exists.

STATEMENT:  INSERT INTO books (id, title, price) VALUES (912312444, 'My title', 20) RETURNING id

LOG:  unexpected EOF on client connection

----

my code is 

         var id int64

err := db.QueryRow(sqlStr).Scan(&id)

if err != nil {

//panic(err)

log.Println(err.Error())

return

}

---

I am getting detailed error if I use Exec()

2012/07/12 18:40:14 INSERT INTO books (id, title, price) VALUES (12312444, 'My title', 20)

2012/07/12 18:40:14 pq: C:"23505" D:"Key (id)=(12312444) already exists." F:"nbtinsert.c" M:"duplicate key value violates unique constraint \"books_pkey\"" L:"397" S:"ERROR" R:"_bt_check_unique"

But, I like to use Query to get the newly inserted row id.

Is there a way I can get more explicit error than (sql: no rows in result set)

thanks

bsr.

[Jeff Hodges]

Yeah, the trick that will get you moving is:

var pgErrCodeByteKey = 'C' // define somewhere near your constants
var pgUniquenessErrCode = "23505"
....

err := db.QueryRow(sqlStr).Scan(&id)
if err != nil {

pqErr, ok := err.(*pq.PGError)
if ok && pqErr.Get(pgErrCodeByteKey) != pgUniquenessErrCode {
// handle uniqueness violation
}
}

What this is doing is pulling out the postgres error code out of the
error struct. The error codes are document here:
<http://www.postgresql.org/docs/9.1/static/errcodes-appendix.html>.

A better solution would probably be for pq.PGError to expose some of
this. I'm not sure what the best API would look like. Almost certainly
a *PGError.ErrorCode() method, and maybe a lookup table for them.

[Jeff Hodges]

That's definitely supposed to be "== pgUniquenessErrCode" there.

[Jeff Hodges]

Are you sure that you're being returned a PGError? Implicit in my code
sample was that you knew you had a PGError, which is not the only
error that pq will return. You'll certainly have to handle other
types.

On Fri, Jul 13, 2012 at 4:38 AM, <pelletie...@gmail.com> wrote:
> Hello,
>
> I am in the same situation as bsr is. I tried Jeff's solution, yet 'ok' is
> always equal to false, and pqErr is nil.
> Any idea of what I am doing wrong?
>
> Cheers,
> Thomas

[bsr]

Jeff. thanks for your suggestion.

I am yet to try it, but looking at the code, I guess the error is not mapping in case of query.

the driver calls "parseError" to map the error.

https://github.com/bmizerany/pq/blob/master/conn.go#L395

But for query,

func (st *stmt) Query(v []driver.Value) (_ driver.Rows, err error) {
	defer errRecover(&err)
	st.exec(v)
	return &rows{st: st}, nil
}

type row, or stmt doesn't have an error field, so I guess the error is skipped.

As per QueryRow "Errors are deferred until Row's Scan method is called."

type rows struct {

	st   *stmt
	done bool
}

Now, it works if you use Query (instead of QueryRow)

var id int64

r, err := c.db.Query(sqlStr)

if err != nil {

log.Println(err.Error())

return err

}

if !r.Next() {

log.Println("expected row")

}

err = r.Scan(&id)

       if err != nil {

log.Println(err.Error())

return err

}

Error:  pq: C:"23505" D:"Key (id)=(912312444) already exists." F:"nbtinsert.c" M:"duplicate key value violates unique constraint \"books_pkey\"" L:"397" S:"ERROR" R:"_bt_check_unique"

may be the author comments, else raise an issue at github.

thanks.

[bsr]

Jeff, what I meant to say was , I tried your suggestion and it works with Query and not QueryRow.

I started writing the previous message before I try it out, but towards the end I confirmed it :-)

[Jeff Hodges]

Oh, yeah, sorry. Too many copy-paste bugs last night. Sorry, dude!