如何避免0（零）int转换为Postgres“空”值并违反“非空”约束？

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
}

Answer 1

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.