Pg: GO false value translates to PostgreSQL null

[threeaccents]

I have a boolean column in my database that has a constraint of not-null. I don't know if this is a bug in the core sql driver itself but since in GO false in considered the bool nil value when ever I try to add or update the table with a false value I get this error:

ERROR #23502 null value in column "done" violates not-null constraint (addr="[::1]:5432")

[vmihailenco]

You need to add notnull annotation:

MyBool bool `sql:",notnull"`

Upd: in v9 you should use pg:",use_zero" to achieve the same:

MyBool bool `pg:",use_zero"`

[Till--H]

@vmihailenco I've got a similar problem but additionally my colums are a composite primary key. How do I add the pk to this, additionally to the notnull? Is there any documentation on how to combine these sql:".." statements?

X int `sql:",notnull"`
Y int `sql:",notnull"`

[vmihailenco]

Replace ,notnull with ,pk.

Is there any documentation on how to combine these sql:".." statements?

Did you read the readme? It has plenty of examples.

[Till--H]

I did read the readme, but couldn't find any example of ,notnull and ,pk combined. When I only use ,pk, I get the following error with the example code below. Most likely it is just me using it wrong, but ,pk seems to not imply ,notnull.

ERROR:  null value in column "x" violates not-null constraint
DETAIL:  Failing row contains (null, null, foo).
STATEMENT:  INSERT INTO "tests" ("x", "y", "something") VALUES (DEFAULT, DEFAULT, 'foo') RETURNING "x", "y"

package main

import (
    "fmt"

    "gopkg.in/pg.v5"
)

type Test struct {
    X         int `sql:",pk"`
    Y         int `sql:",pk"`
    Something string
}

func createSchema(db *pg.DB) error {
    _, err := db.Exec(`CREATE TEMP TABLE tests (x integer, y integer, something text, PRIMARY KEY(x, y))`)
    if err != nil {
        return err
    }

    return nil
}

func main() {
    db := pg.Connect(&pg.Options{
        User: "postgres",
    })
    if err := createSchema(db); err != nil {
        fmt.Printf("Error creating schema: %v", err)
    }
    if err := db.Insert(&Test{X: 0, Y: 0, Something: "foo"}); err != nil {
        fmt.Printf("Error inserting: %v", err)
    }
    fmt.Printf("Done")
}

[vmihailenco]

Yeah, I see. go-pg just assumes that 0 does not make much sense as primary key and expects pk to be generated by the server, .e.g. as if the field is serial.

I guess you want here a combination of ,pk,notnull. I just need to make sure it works as expected...

[vmihailenco]

I just checked and ,pk,notnull works as expected. The default behavior is probably not obvious, but it is useful and some users already rely on it.

[Till--H]

Ok, thanks. It does not work for me with my above example (when changing the sql:",pk" to sql:",pk,notnull"), but there might be some other issue. I'll check that.

[fi0]

Is there a way to apply notnull globally?

[afiskon]

Guys, you have an error in the documentation https://github.com/go-pg/pg/wiki/Model-Definition according to it I have to use pg:",notnull" instead of sql:",notnull"but pg: doesn't work.

[vmihailenco]

See https://github.com/go-pg/pg/blob/master/CHANGELOG.md - it is now pg:",use_zero" to use zero values instead of NULL. pg:",notnull" only controls NOT NULL SQL constraint.

[hooksoft]

Its not clear: I can both use_zero and notnull, right? Use zero doesn't affect the schema/database side, if I understand correctly.

[vmihailenco]

Yes, they control different aspects and can be used together.