Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

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

database/sql: Impossible to insert a jsonb field #33345

Closed
marticrespi opened this issue Jul 29, 2019 · 3 comments
Closed

database/sql: Impossible to insert a jsonb field #33345

marticrespi opened this issue Jul 29, 2019 · 3 comments

Comments

@marticrespi
Copy link

@marticrespi marticrespi commented Jul 29, 2019

What version of Go are you using (go version)?

$ go version
go version go1.11.1 windows/amd64

Does this issue reproduce with the latest release?

I haven't reproduced it with last version, at this moment, I can't upgrade it.

What operating system and processor architecture are you using (go env)?

go env Output
$ set GOARCH=amd64
set GOBIN=
set GOCACHE=C:\Users\XXXXX\AppData\Local\go-build
set GOEXE=.exe
set GOFLAGS=
set GOHOSTARCH=amd64
set GOHOSTOS=windows
set GOOS=windows
set GOPATH=D:\TFS\Go
set GOPROXY=
set GORACE=
set GOROOT=C:\Go
set GOTMPDIR=
set GOTOOLDIR=C:\Go\pkg\tool\windows_amd64
set GCCGO=gccgo
set CC=gcc
set CXX=g++
set CGO_ENABLED=1
set GOMOD=D:\TFS\Go\src\XXXXXXX\go.mod
set CGO_CFLAGS=-g -O2
set CGO_CPPFLAGS=
set CGO_CXXFLAGS=-g -O2
set CGO_FFLAGS=-g -O2
set CGO_LDFLAGS=-g -O2
set PKG_CONFIG=pkg-config
set GOGCCFLAGS=-m64 -mthreads -fno-caret-diagnostics -Qunused-arguments -fmessage-length=0 -fdebug-prefix-map=C:\Users\XXXXXXX\AppData\Local\Temp\go-build620489658=/tmp/go-build -gno-record-gcc-switches

What did you do?

Here is my main struct in this loop where StatsInfo and CheckinBookingInfo is working well parsing them in another calls..

type DataResponseTree struct {
	StartDate          string            `json:"startDate"`
	EndDate            string            `json:"endDate"`
	StatsInfo          *StatsResponse    `json:"statsInfo"`
	CheckinBookingInfo *BookingsResponse `json:"checkinBookingInfo"`
}

I'm trying to insert a jsonb field but I can't. I'm trying it like this

if i.Product.ProductDataResponse != nil {
for date, response := range i.Product.ProductDataResponse {
	responseJSON, err := json.Marshal(response)
	fmt.Print(string(responseJSON))
	if err == nil {
		// Guardamos las respuestas de stats e insights por fecha.
		insert := `
		INSERT INTO billing."ProductResponse"
		("date", "response", createdAt", "updatedAt", "productId")
		VALUES($1, $2, $3, $3, $4);`

		_, err := sql.Instance.DB.Exec(insert, date, string(responseJSON), now, i.Product.ID)
		if err != nil {
			return err
		}
	}
}

And the fmt line shows me a valid json

{
    "startDate" : "2019-06-01T00:00:00Z",
    "endDate" : "2019-06-30T23:59:59Z",
    "statsInfo" : {
        "stats" : {
            "report" : {
                "edges" : [
                    {
                        "node" : {
                            "statsData" : {
                                "operations" : [
                                    {
                                        "operation" : {
                                            "operationData" : {
                                                "code" : "Disponibilidad",
                                                "types" : [
                                                    "SEARCH"
                                                ],
                                                "api" : {
                                                    "code" : "hotel"
                                                }
                                            }
                                        },
                                        "totalHits" : 18867219,
                                        "trafficType" : "BASIC",
                                        "detailedHits" : [
                                            {
                                                "time" : 0,
                                                "code" : "207",
                                                "type" : "PROVIDER",
                                                "hits" : 290485
                                            },
                                            {
                                                "time" : 1171,
                                                "code" : "0",
                                                "type" : "OK",
                                                "hits" : 11238192
                                            },
                                            {
                                                "time" : 220,
                                                "code" : "102",
                                                "type" : "PROVIDER",
                                                "hits" : 12763
                                            },
                                            {
                                                "time" : 22407,
                                                "code" : "104",
                                                "type" : "COMMUNICATION",
                                                "hits" : 3042
                                            },
                                            {
                                                "time" : 20258,
                                                "code" : "105",
                                                "type" : "COMMUNICATION",
                                                "hits" : 1817
                                            },
                                            {
                                                "time" : 552,
                                                "code" : "204",
                                                "type" : "PROVIDER",
                                                "hits" : 7320865
                                            },
                                            {
                                                "time" : 31143,
                                                "code" : "3",
                                                "type" : "HUB",
                                                "hits" : 55
                                            }
                                        ]
                                    }
                                ]
                            },
                            "adviseMessage" : [
                            ]
                        }
                    }
                ]
            }
        }
    },
    "checkinBookingInfo" : {
        "bookings" : {
            "bookingsSummary" : null
        }
    }
}

But when I run the exec line, it throws me an error:

image

What am I doing wrong? Or someone can tell me if it's a bug from a non updated version?

This insert works perfectly through an IDE database.

@toothrot
Copy link
Contributor

@toothrot toothrot commented Jul 29, 2019

For questions about Go, you might have better luck on a different forum: https://golang.org/wiki/Questions

Looking at this, I see a typo in ("date", "response", createdAt", "updatedAt", "productId"): createdAt is quoted incorrectly, which is consistent with the error message you reported.

I would also suggest that you mention the database driver you are using (for example, lib/pq).

@toothrot toothrot added the Question label Jul 29, 2019
@toothrot toothrot changed the title Impossible to insert a jsonb field database/sql: Impossible to insert a jsonb field Jul 29, 2019
@marticrespi
Copy link
Author

@marticrespi marticrespi commented Jul 30, 2019

Hours trying to solve this insert, and is due the missing double quotes, but I was confused with the error message.

Many thanks @toothrot !

@toothrot
Copy link
Contributor

@toothrot toothrot commented Jul 30, 2019

No problem. Glad it worked, good luck!

@golang golang locked and limited conversation to collaborators Jul 29, 2020
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
3 participants
You can’t perform that action at this time.