Error 23502 null value in column

INSERT INTO "example" ("uid","name","slug","website","updated_at","created_at") VALUES ('c4fenn594816lfvs44t0','t1629416412','t1629416412','',1629416412,1629416412) RETURNING "example_id"

ERROR: null value in column "website" of relation "example" violates not-null constraint (SQLSTATE 23502)

create table if not exists example (
  example_id bigserial primary key,
  uid varchar(20) default '' not null,
  name varchar(255) default '' not null,
  slug varchar(500) default '' not null,
  website varchar(1000) default '' not null,
  updated_at bigint default 0 not null,
  created_at bigint default 0 not null,
  unique (uid),
  unique (name),
  unique (slug)
);

After a bit of poking around, it seems that if I remove PreferSimpleProtocol: true then it starts working.

Any insight as to how to fix without removing this setting would be great.

This is surely super weird.

Struct I have

type Schema struct {
	gorm.Model
	Name    string `gorm:"column:name;uniqueIndex;type:varchar(45);NOT NULL"`
	Memo    string `gorm:"column:memo;type:text;NOT NULL"`
	Website string
}

I added not null constraint on the db and here it goes:

The field having a gorm tag did not matter for me. It is working with an empty string

I added a large char field with default value to replicate your column but still it works:

Quoting PreferSimpleProtocol from pgx

// PreferSimpleProtocol disables implicit prepared statement usage. By default pgx automatically uses the extended
// protocol. This can improve performance due to being able to use the binary format. It also does not rely on client
// side parameter sanitization. However, it does incur two round-trips per query (unless using a prepared statement)
// and may be incompatible proxies such as PGBouncer. Setting PreferSimpleProtocol causes the simple protocol to be
// used by default. The same functionality can be controlled on a per query basis by setting
// QueryExOptions.SimpleProtocol.

gorm version: v1.21.13
psql version: 13.4

@Akaame Thank you for the taking the time to try and reproduce + the screenshots. From my understanding, it looks like it’s hard to reproduce the issue, so let me try to give a more concrete example:

The Postgres table:

create table if not exists examples (
  example_id bigserial primary key,
  test varchar(2038) default '' not null,
  categories integer[],
  updated_at bigint default 0 not null,
  created_at bigint default 0 not null
);

Code to insert one row:

package main

import (
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
	"log"
	"strings"
)

func main() {
	db, err := gorm.Open(postgres.New(postgres.Config{
		DSN: strings.Join([]string{
			"host=localhost",
			"dbname=app",        // @TODO
			"user=postgres",     // @TODO
			"password=password", // @TODO
			"port=5432",
			"sslmode=disable",
		}, " "),
		PreferSimpleProtocol: true,
	}), &gorm.Config{
		SkipDefaultTransaction: true,
		PrepareStmt:            false,
		Logger:                 logger.Default.LogMode(logger.Info),
	})
	if err != nil {
		log.Fatal(err)
	}

	type Example struct {
		ExampleID int64   `json:"exampleId" gorm:"primaryKey"`
		Test      *string `json:"test"`
		UpdatedAt int64   `json:"updatedAt" gorm:"autoUpdateTime"`
		CreatedAt int64   `json:"createdAt" gorm:"autoCreateTime"`
	}

	test := ""
	example := Example{
		Test: &test,
	}

	if err := db.Create(&example).Error; err != nil {
		log.Fatal(err)
	}

	log.Println("Done.")
}

My personal go.mod:

module pgtest

go 1.17

require (
	github.com/lib/pq v1.10.2
	gorm.io/driver/postgres v1.1.0
	gorm.io/gorm v1.21.13
)

require (
	github.com/jackc/chunkreader/v2 v2.0.1 // indirect
	github.com/jackc/pgconn v1.10.0 // indirect
	github.com/jackc/pgio v1.0.0 // indirect
	github.com/jackc/pgpassfile v1.0.0 // indirect
	github.com/jackc/pgproto3/v2 v2.1.1 // indirect
	github.com/jackc/pgservicefile v0.0.0-20200714003250-2b9c44734f2b // indirect
	github.com/jackc/pgtype v1.8.1 // indirect
	github.com/jackc/pgx/v4 v4.13.0 // indirect
	github.com/jinzhu/inflection v1.0.0 // indirect
	github.com/jinzhu/now v1.1.2 // indirect
	golang.org/x/crypto v0.0.0-20210711020723-a769d52b0f97 // indirect
	golang.org/x/text v0.3.6 // indirect
)

This is what I get when I try to run the program:

The interesting thing is that if I try to copy and paste the generated SQL manually into Postgres CLI, it works! And if I comment out the line PreferSimpleProtocol: true, then the code also works.

I’m not sure whether the issues is with PreferSimpleProtocol or something else. Maybe someone else can see something else I don’t?

PS: You might’ve noticed that my gorm.Open config settings are somewhat non-standard. I’m simply trying not to use prepared statements so that I can edit Postgres tables manually without having to restart the Golang server in the real project.

@jinzhu This started out as a question, but we managed to create a consistent reproducible example (above), which seems like this could be a bug (not sure if in gorm or somewhere down the line). Any chance you could take a look and advise on something?

Hello,

Sorry I was busy with work. I will try to take a look at it sometime today.

Cheers.

Edit: Sorry I thought you were referring to me.

I thought you had problem with PSQL Extended Protocol. But you explicitly want to use the simple protocol to shut down prepared statements completely (Correct me if my reasoning here is false). I think the reason PSQL client is able to execute your query is probably it defaults to Extended Protocol.

I will try and replicate your setup but this hits me more like a driver issue. This territory is quite uncharted for me.

Second edit I replicated you issue on my local.

When I change it to test := «a». It works perfectly.

@Akaame,

Second edit I replicated you issue on my local … When I change it to test := «a». It works perfectly.

Yep, exactly

But you explicitly want to use the simple protocol to shut down prepared statements completely (Correct me if my reasoning here is false).

That’s correct. And to elaborate a bit: I’m not entirely sure what protocol I want/need(?). I tried to disable prepared statements simply because I kept getting an error that the SQL schema has changed and I had to restart the Go server every time (I didn’t want the same issue in production). I’d just like to manually adjust SQL schemas in CLI without having to restart the Go server/connections — and the simplest way to do that seems to be to disable prepared statements(?).

tl&dr: Having a hard time inserting empty strings when prepared statements are disabled.

@Akaame, did you get a chance to check it out?

P.S: Are you one of the maintainers or this repo or just helping out? I’m curious cause would be nice to put a «bug» label on the issue, since it seems like one, but I’m not able to apply one.

@Akaame Hello I had some personal matters to attend to. And no I am not a maintainer. I will update here with investigarions if/when I have time.

This issue has been automatically marked as stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 30 days