pq: invalid input syntax for type json When setup default value tag for json.RawMessage

[twsiyuan]

This is the my testing example which set up the database using dockertest:

package main

import (
	"encoding/json"
	"fmt"
	"log"
	"net/url"
	"os"
	"time"

	"github.com/jinzhu/gorm"
	_ "github.com/lib/pq"
	"github.com/ory/dockertest"
	gormbulk "github.com/t-tiger/gorm-bulk-insert/v2"
)

type ExampleModel struct {
	gorm.Model

	Data json.RawMessage `gorm:"not null;default:'{}'"`
}

func main() {
	r, err := dockerRDS()
	if err != nil {
		log.Fatalf("Failed to create rds connection: %v", err)
	}
	defer r.Release()
	db := r.DB

	err = db.AutoMigrate(&ExampleModel{}).Error
	if err != nil {
		log.Fatalf("Failed to migrate model: %v", err)
	}

	// Start debugging
	db = db.Debug()

	// INSERT INTO "example_models" ("created_at","updated_at","deleted_at")
	// VALUES ('2020-05-15 15:31:06','2020-05-15 15:31:06',NULL) RETURNING "example_models"."id"
	err = db.Save(&ExampleModel{}).Error
	if err != nil {
		log.Fatalf("Failed to save: %v", err)
	}

	// Error: pq: invalid input syntax for type json
	//
	// INSERT INTO "example_models" ("created_at", "data", "deleted_at", "updated_at")
	// VALUES ('2020-05-15 15:25:51', ''{}'', NULL, '2020-05-15 15:25:51')
	//                                 ????
	err = gormbulk.BulkInsert(db, []interface{}{
		&ExampleModel{},
	}, 1000)
	if err != nil {
		log.Fatalf("Failed to bulk insert: %v", err)
	}
	log.Print("DONE")
}

type docker struct {
	DB  *gorm.DB
	URL string

	pool     *dockertest.Pool
	resource *dockertest.Resource
}

func (d docker) Release() error {
	return d.pool.Purge(d.resource)
}

func dockerRDS() (*docker, error) {
	pool, err := dockertest.NewPool("")
	if err != nil {
		return nil, fmt.Errorf("failed to create docker client: %w (have you run the docker daemon?)", err)
	}
	pool.MaxWait = time.Second * 10

	resource, err := pool.Run("postgres", "9.4.20-alpine", []string{"POSTGRES_PASSWORD="})
	if err != nil {
		return nil, fmt.Errorf("failed to run docker container: %w", err)
	}

	var rdsURL string
	var rdsDB *gorm.DB
	if err := pool.Retry(func() error {
		host := resource.GetBoundIP("5432/tcp")
		if h := os.Getenv("DOCKER_HOST"); h != "" {
			u, err := url.Parse(h)
			if err == nil {
				host = u.Hostname()
			}
		}

		port := resource.GetPort("5432/tcp")
		url := fmt.Sprintf("postgres://postgres@%s:%s/postgres?sslmode=disable", host, port)

		db, err := gorm.Open("postgres", url)
		if err != nil {
			return err
		}

		rdsURL = url
		rdsDB = db

		return nil
	}); err != nil {
		pool.Purge(resource)
		return nil, fmt.Errorf("failed to connect to the docker container: %w", err)
	}

	return &docker{
		DB:       rdsDB,
		URL:      rdsURL,
		pool:     pool,
		resource: resource,
	}, nil
}

How to test

• Copy the example code above into a main.go
• Call go mod init under the same folder to setup the package dependency tool
• Install Docker if you don't have, or rewrite the function dockerRDS to provide a *gorm.DB
• Run go run main.go

Issue

I saw the SQL when calling db.Save(...) is correct:

INSERT INTO "example_models" ("created_at","updated_at","deleted_at") 
VALUES ('2020-05-15 15:35:34','2020-05-15 15:35:34',NULL) 
RETURNING "example_models"."id" 

But found invalid SQL when calling gormbulk.BulkInsert(db, ....):

INSERT INTO "example_models" ("created_at", "data", "deleted_at", "updated_at") 
VALUES ('2020-05-15 15:35:34', ''{}'', NULL, '2020-05-15 15:35:34')

The error:

pq: invalid input syntax for type json

[bombsimon]

Sorry, I should probably test this before answering, but what happens if you remove your quotes from the tag? It seems like your result gets double quoted.

Data json.RawMessage `gorm:"not null;default:{}"`

Also, it seems like gorm omits the fields. You can pass fields to omit to gormbulk. I guess this is a workaround rather than a solution but you can check if your JSON field is empty and exclude it if so. Just for troubleshooting purposes, does this resolve the issue in your example?

gormbulk.BulkInsert(db, []interface{}{&ExampleModel{}}, 1000, "data")

I'll look into this and test more throughly when I get some spare time!

[twsiyuan]

You will get pq: syntax error at or near "{" error when removed the quoted.

type ExampleModel struct {
	gorm.Model

	Data json.RawMessage `gorm:"not null;default:{}"`
}

[twsiyuan]

Interesting, I found another issue if I changed the insert value as:

testData := &ExampleModel{Data: ([]byte)(`{"key":"value"}`)}

It works when calling db.Save(testData):

INSERT INTO "example_models" ("created_at","updated_at","deleted_at","data") 
VALUES ('2020-05-15 16:46:57','2020-05-15 16:46:57',NULL,'[123 34 107 101 121 34 58 34 118 97 108 117 101 34 125]') 
RETURNING "example_models"."id"  

But failed when calling gormbulk.BulkInsert(db, []interface{}{.testData }):

INSERT INTO "example_models" ("created_at", "data", "deleted_at", "updated_at") 
VALUES ('2020-05-15 16:46:58', 123,34,107,101,121,34,58,34,118,97,108,117,101,34,125, NULL, '2020-05-15 16:46:58')   

The error:

pq: INSERT has more expressions than target columns

[bombsimon]

Thanks for the test! This does not really surprise me since gormbulk just uses the raw values from a struct and passes as arguments to db.Exec. I've not worked with Postgres and JSON in a long time so I have to dig deeper and also look into what the pq driver/gorm does with these data types.

[AbhisheKundalia]

any workaround to work with?

[t-tiger]

Thanks for reporting @twsiyuan. I am investigating this issue.

Actually, I don't fully understand the internal behaviors of GORM, and I'm trying to figure out a good way to fix this.

I've found that there are two problems around this.

1. json.RawMessage is not saved with proper text
2. without tag of default value, null is not inserted into json field

Altough it's just a workaround, to avoid first problem, you can pass uint8[] instead of json.RawMessage to insert successfully.

For the second, however, can only be avoided by modifying the internals of this library.

In any case, using uint8[] requires users to have knowledge of the internal implementation, so I want to come up with a good solution in the near term.