CODEWITHSUNDEEP
postgresqlgosql-null
Philipp Sebastian
Philipp Sebastian

Reputation: 51

How to avoid that 0 (zero) int turns into Postgres "null" value and violates "not null" constraint?

In Go, I am unmarshalling/decoding JSON into a struct with an ID field of type int. Then I try to insert this struct into a PostgreSQL database using go-pg with the ID column as the primary key (which has a not-null constraint). The first entry has a 0 as its ID. In the Postgres documentation, it states that 0 is ok as a value of a primary key. However, I keep getting an error message:

"ERROR #23502 null value in column "number" violates not-null constraint".

It looks like the 0 turns into a Go "zero value" when it is unmarshalled into the int value. Then it is inserted as null value into Postgres. Any tips on how I might be able to avoid this would be greatly appreciated.

type Account struct {
   Number int `sql:"type:smallint, pk"`
   Name string
}

[...]

account := Account{}
err := json.NewDecoder(r.Body).Decode(&account)

[...]

insertErr := pgLayer.db.Insert(&account)
if insertErr != nil {
   log.Printf("Error while inserting new item")
   return "n/a", insertErr
}

Upvotes: 5

Views: 5414

Answers (2)

Ewan
Ewan

Reputation: 15058

While it's not immediately obvious with go-pg you can use the struct tag sql:",notnull" to show that Go empty values ("", 0, [] etc.) are allowed and should not be treated as SQL NULL.

You can see it in the Features list.

In your case I would change this to:

type Account struct {
   Number int `sql:"type:smallint,pk,notnull"`
   Name string
}

Upvotes: 2

George Edward Shaw IV
George Edward Shaw IV

Reputation: 835

I think the easiest solution to your problem is to make your ID column of type SERIAL and let Postgres deal with setting and auto-incrementing the value for you. If you need the value within your application directly after inserting it, you can always use a RETURNING psql clause, like such:

INSERT INTO shows(
    user_id, name, description, created, modified
) VALUES(
    :user_id, :name, :created, :modified
) RETURNING id;

And capture the response within your code.

Upvotes: -1

Related Questions