rows.Scan null values in database/sql error

[Milan P. Stanic]

Hi,

I tried simple SQL test with postgresql driver from
github.com/bmizerany/pq

The code is here:

package main

import (
"database/sql"
"fmt"
_ "github.com/bmizerany/pq"
)

func main() {
db, err := sql.Open( "postgres", "host=fx dbname=smafa user=postgres
password=qwe sslmode=disable" )
if err != nil {
fmt.Println(err)
return
}
defer db.Close()

rows, err := db.Query( "SELECT 1 as id, null as name" )
if err != nil {
fmt.Println(err)
return
}
defer rows.Close()
for rows.Next() {
var id int
var name string
err = rows.Scan( &id, &name )
if err != nil {
fmt.Println(err)
}
fmt.Println( id, name )
}
rows.Close()
}

Result is here:
./gosql
sql: Scan error on column index 1: unsupported driver -> Scan pair: <nil> -> *string
1

So, rows.Scan cannot assign null/nil value to a string var and AFAIK
this is the golang feature (or design mistake as someone could think).

Working with SQL there are cases when column does not have value, i.e.
it is NULL and code should be able to work with such cases.

In my example above, if var name is set to empty string ("") it does
not convey actual data from DB, so that option would be wrong IMHO.

I read about using RawBytes from the MySQL example at:
https://github.com/Go-SQL-Driver/MySQL/wiki/Examples
but I think it is some kind of workaround and not the proper solution.

My question: is there a way to solve that problem in 'proper way'.

--
Kind regards, Milan

[chris dollin]

On 20 February 2013 10:03, Milan P. Stanic <m...@arvanta.net> wrote:

> Result is here:
> ./gosql
> sql: Scan error on column index 1: unsupported driver -> Scan pair: <nil> -> *string
> 1
>
> So, rows.Scan cannot assign null/nil value to a string var and AFAIK
> this is the golang feature (or design mistake as someone could think).

Suurely `name` should be a NullString if you want to get a possibly-null
string?

Chris

--
Chris "allusive" Dollin

[Julien Schmidt]

Yes, like Chris says, http://golang.org/pkg/database/sql/#NullString is what you are looking for. RawBytes is for some special use cases.

[Tor Langballe]

Note that if you need to SELECT timestamps, use the NullTime provided in the source in

github.com/bmizerany/pq 
(or roll your own)

As there isn't any NullTime in the standard sql package.

tor

[Milan P. Stanic]

chris, Julien and Tor, thank you for the hint.

Solution isn't perfect IMHO, but I can go with it.

--
Kind regards, Milan

[sati...@gmail.com]

But why is this designed this way? I am very new to go and I believe there is a "nil" in go. Why isn't a NULL value from the database simply mapped to nil? It seems too cumbersome to deal with something as simple as a nil. Database admins prefer NULL over empty strings because they say it reduces the size of the database overall.

[Andy Balholm]

On Friday, April 4, 2014 9:48:46 AM UTC-7, sati...@gmail.com wrote:

But why is this designed this way? I am very new to go and I believe there is a "nil" in go. Why isn't a NULL value from the database simply mapped to nil? It seems too cumbersome to deal with something as simple as a nil. Database admins prefer NULL over empty strings because they say it reduces the size of the database overall.

I think the sql package scans NULL as nil if the type it is scanning into can be nil. Strings can't. 

[danm...@gmail.com]

I echo this point. Why can't string be assigned to nil? Should I just get used to using []byte? It just seems way to common to have a nullable string field and way to cumbersome to have use a struct with an extra bool every time I want a basic string. Has this changed since posting?

[Dave Cheney]

Go strings are values, not references, so nil is not part of the set of valid strings. Coming from a Java background, I'm very happy about this.

[Chris Kastorff]

I use *string with database/sql when I need a nullable TEXT-y field.

On 03/23/2015 07:49 PM, danm...@gmail.com wrote:
> I echo this point. Why can't string be assigned to nil? Should I just
> get used to using []byte? It just seems way to common to have a nullable
> string field and way to cumbersome to have use a struct with an extra
> bool every time I want a basic string. Has this changed since posting?
>
>
>
> On Friday, April 4, 2014 at 11:48:46 AM UTC-5, sati...@gmail.com wrote:
>
> But why is this designed this way? I am very new to go and I believe
> there is a "nil" in go. Why isn't a NULL value from the database
> simply mapped to nil? It seems too cumbersome to deal with something
> as simple as a nil. Database admins prefer NULL over empty strings
> because they say it reduces the size of the database overall.
>
> On Thursday, February 21, 2013 7:28:37 AM UTC-8, aiee wrote:
>
> chris, Julien and Tor, thank you for the hint.
>
> On Wed, 2013-02-20 at 13:41, Tor Langballe wrote:
> > Note that if you need to SELECT timestamps, use the NullTime
> provided in
> > the source in

> > github.com/bmizerany/pq <http://github.com/bmizerany/pq>

> > (or roll your own)
> > As there isn't any NullTime in the standard sql package.
>
> Solution isn't perfect IMHO, but I can go with it.
>
> --
> Kind regards, Milan
>

> --
> You received this message because you are subscribed to the Google
> Groups "golang-nuts" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to golang-nuts...@googlegroups.com
> <mailto:golang-nuts...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

[m...@ualberta.ca]

Has anyone found a better solution to this besides using the NullString struct? personally not a fan of that as it requires me to separately handle returned boolean values

> <mailto:golang-nuts+unsub...@googlegroups.com>.

[Leopoldo Caballero]

I did this:

https://github.com/pitakill/go_api/blob/devel/person/person.go#L51

The important part is that the struct Person, use a pointer string in the fields that maybe/maybe not be null from the Database