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

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

绅士的闹钟 · 环境设计专业就业前景好的大学排名_艺术类硕士 ...· 2 月前 ·

跑龙套的太阳 · 如何管理iPhone 或iPad ...· 3 月前 ·

豪情万千的羽毛球 · 以家人之名，男歌手周正宝携《我敬爱的爸妈》上 ...· 5 月前 ·

豪爽的牛肉面 · 摩刻MOK划船机M1开箱,感受一下性价比_划 ...· 8 月前 ·

近视的遥控器 · 索尼音乐娱乐中国| Sony Music ...· 1 年前 ·

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement . We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account _ "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

changed the title ~~pq: invalid input syntax for type json When setup default tags for json.RawMessage~~


    pq: invalid input syntax for type json

When setup default value tag for


    json.RawMessage

May 15, 2020

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!

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:{}"`
          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
          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.
json.RawMessage is not saved with proper text
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.