将Golang JSON存储到Postgresql中 [英] Storing Golang JSON into Postgresql

问题描述

我想在数据库中存储一个带有JSON字段的结构.

I want to store a certain struct into my database that has a JSON field within it.

type Comp struct {
    CompId               int64           `db:"comp_id" json:"comp_id"`
    StartDate            time.Time       `db:"start_date" json:"start_date"`
    EndDate              time.Time       `db:"end_date" json:"end_date"`
    WeeklySchedule       json.RawMessage `db:"weekly_schedule" json:"weekly_schedule"`
}

该表的架构为:

CREATE TABLE IF NOT EXISTS Tr.Comp(
    comp_id                 SERIAL,
    start_date              timestamp NOT NULL,
    end_date                timestamp NOT NULL,
    weekly_schedule         json NOT NULL,
    PRIMARY KEY (comp_id)
);

我在项目中使用sqlx和lib/pq驱动程序，并且不会执行以下操作.相反，它惊慌地说有一个nil指针. DB是全局*sqlx.DB结构

I am using sqlx and lib/pq driver in my project and the following will not execute. Instead it panics saying there is a nil pointer. DB is a global *sqlx.DB struct

    tx := DB.MustBegin()

    compFixture := Comp{
        StartDate:            time.Now(),
        EndDate:              time.Now().AddDate(1, 0, 0),
        WeeklySchedule:       json.RawMessage([]byte("{}")),
    }
    _, err = tx.NamedExec(
        `INSERT INTO 
            Tr.Comp(comp_id, 
                start_date, end_date, weekly_schedule) 
            VALUES (DEFAULT, 
                :start_date, :end_date, :weekly_schedule)  
            RETURNING comp_id;`, compFixture)
    if err != nil {
        t.Fatal("Error creating fixture.", err)
    }

当我从架构和固定装置中删除weekly_schedule时，一切运行正常.但是由于某种原因，当包含此字段时，程序会出现紧急情况.关于如何在数据库架构和Go结构中定义weekly_schedule字段的任何想法?

When I remove weekly_schedule from the schema and fixture things run fine. But for some reason, the when this field is included, the program panics. Any idea as to how I should define the weekly_schedule field in both my DB schema and Go struct?

推荐答案

sqlx在github.com/jmoiron/sqlx/types中具有类型JSONText，它将满足您的需求

sqlx has a type JSONText in github.com/jmoiron/sqlx/types that will do what you need

doc (针对JSONText)

doc for JSONText

这篇关于将Golang JSON存储到Postgresql中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！