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

Question

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
}

Did you try `sql:"smallint,pk,notnull"` ? – ifnotak Apr 14 '19 at 20:22 — ifnotak, Apr 14 '19 at 20:22

score 2 · Answer 1 · answered Apr 15 '19 at 01:23

2

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
}

answered Apr 15 '19 at 01:23

Ewan

14,592
6
48
62

These are the things we should avoid when writing libraries. This ends up imposing a specific coding style. In any other language, a 0 is just a zero and doesn't get interpreted as NULL. – TheRealChx101 Jul 09 '22 at 18:18
Since v9 you should use `pg:",use_zero"` to achieve the same. – Thomas Aug 29 '22 at 09:21

score -1 · Answer 2 · answered Apr 14 '19 at 22:38

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.

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

2 Answers2