How do I handle insert of nil value into NOT NULL column?

[n0vice]

Hello. I'm using native sql package with pgx as driver. In database table I have jsonb NOT NULL column. To be able to handle incoming nil I created Value() implementation that provides driver.Value with "{}":

type User struct {
	Username string `db:"username"`
	Password string `db:"password"`
	Metadata JSONb  `db:"jsonb"`
}

type JSONb json.RawMessage

func (j JSONb) Value() (driver.Value, error) {
	if len(j) == 0 {
		j = JSONb("{}")
	}
	return []byte(j), nil
}

func addUser(db *sql.DB, user User) {
	q := `INSERT INTO users (username, password, jsonb) VALUES ($1,$2,$3) RETURNING id`

	var id uint32
	if err := db.QueryRow(q, user.Username, user.Password, user.Metadata).Scan(&id); err != nil {
		log.Fatalf("error creating user %s: %s", user.Username, err)
	}

	log.Println("ID:", id)
}

In those cases when my function receives User without Metadata I fail with ERROR: null value in column "jsonb" of relation "users" violates not-null constraint (SQLSTATE 23502) because the driver.Valuer is not called.

I tried to use string instead of json.RawMessage (which is just alias for []byte) but the problem still stays. I assume pgx doesn't call driver.Valuer also on zero value.

The only option I see here is explicitly calling Value() before executing query, but that seems ugly and results into same code duplication in many places across the project. Maybe there is another way to handle this?

[jackc]

Go nil values are always considered as SQL NULL. This happens before any methods on the type are called.

I would have expected it to work with string as the underlying value though. The zeronull.Text type value automatically translates "" to NULL. You should be able to adapt that to translate "" to {}.

[n0vice]

This problem popped up when switching drivers from lib/pq to pgx. Before it worked, so I was surprised when found out pgx doesn't call Value() on nils.
Making string an underlying type for JSONb in my example also doesn't trigger Value().

Also I'm not fully understanding your suggestion about "adapting" zeronull.Text. Can you please give more details on this? My goal is to "hijack" whatever value I receive from caller and not allow NULL to be sent to DB.

[jackc]

zeronull.Text:

// Value implements the database/sql/driver Valuer interface.
func (src Text) Value() (driver.Value, error) {
	if src == "" {
		return nil, nil
	}
	return string(src), nil
}

If that was changed to:

func (src Text) Value() (driver.Value, error) {
	if src == "" {
		return "{}", nil
	}
	return string(src), nil
}

it would convert "" to "{}" instead of nil.

[n0vice]

Do you meam I should register my own zeronull type and implement Valuer for it?
Is it possible to do without using tooling of pgx? For connections and querying I'm using standard database/sql stuff.

[jackc]

Something like this:

type T string

func (src T) Value() (driver.Value, error) {
	if src == "" {
		return "{}", nil
	}
	return string(src), nil
}

I would have expected this to work.

But after looking into it a little further it looks like the actual problem is different. The problem is pgx knows that it is a json or jsonb column and pgx automatically marshals any value into json columns. So it never gets to call Value(). I think defining MarshalJSON would actually do what you want. e.g.

func (src T) MarshalJSON() ([]byte, error) {
	if string(src) == "" {
		return []byte("{}"), nil
	}
	return []byte(src), nil
}

[n0vice]

You were right. MarshalJSON() got called. Thanks!