Create table in Postgres with json type field using GoLang

Question

I am using GoLang in the back-end and PostgreSQL as a database. I'm new to PostgreSQL database connections with Go. I'm using Beego as a back-end. I want to create a table with one of a fields of JSON type using Golang database/sql package and lib/pq. That what I do

This is my create table query

createtable:= `CREATE TABLE apply_leave1 (
                leaveid serial PRIMARY KEY NOT NULL ,
                empid varchar(10) NOT NULL ,
                leavedays double precision NOT NULL DEFAULT 0 ,
                mdays double precision NOT NULL DEFAULT 0 ,
                leavetype varchar(20) NOT NULL DEFAULT '' ,
                daytype text NOT NULL '',
                leavefrom timestamp with time zone NOT NULL,
                leaveto timestamp with time zone NOT NULL,
                applieddate timestamp with time zone NOT NULL,
                leavestatus varchar(15) NOT NULL DEFAULT ''  ,
                resultdate timestamp with time zone,
                certificatestatus bool NOT NULL DEFAULT FALSE 
                certificate json NULL)`



    conn := fmt.Sprintf(
        "user=%s password=%s dbname=%s sslmode=disable",
        "postgres",
        "root",
        "employee")
    log.Println("Creating a new connection: %v", conn)

    db, err := sql.Open("postgres", conn)

    stmt, err1 := db.Prepare(createtable)

    defer stmt.Close()
    _, err = stmt.Exec()
    if err != nil {
        fmt.Println(err.Error()
    }

}

This is throwing me the following error

Handler crashed with error runtime error: invalid memory address or nil pointer dereference

But when I'm using query to select something from the table there is no problem with table creation query or other executed sql code. I appreciate any help. Thanks!

Please simplify your code snippet or provide a concrete line which raises the error. We can't build all your application and debug it. — I159, Nov 28 '16 at 10:03
And one more significant thing - your error doesn't related to sql or any libs you use. It is just a wrong pointer operation. — I159, Nov 28 '16 at 10:06
sorry.Beginner on stack overflow. Thanks for your advice – I159 .Edited the code to be precise.May i know which pointer is the problem.Thanks — Rajesh Kumar, Nov 28 '16 at 10:16
it is OK, just read the tutorial "how to ask" on stackoverflow. I will try to help you. — I159, Nov 28 '16 at 10:26
Please specify the line which crashes with the mentioned error. — I159, Nov 28 '16 at 14:13

score 0 · Answer 1 · answered Nov 28 '16 at 10:15

I think that your problem is the invalid sql used to create the table, it should be:

CREATE TABLE apply_leave1 (
    leaveid serial PRIMARY KEY NOT NULL ,
    empid varchar(10) NOT NULL ,
    leavedays double precision NOT NULL DEFAULT 0 ,
    mdays double precision NOT NULL DEFAULT 0 ,
    leavetype varchar(20) NOT NULL DEFAULT '' ,
    daytype text NOT NULL DEFAULT '',
    leavefrom timestamp with time zone NOT NULL,
    leaveto timestamp with time zone NOT NULL,
    applieddate timestamp with time zone NOT NULL,
    leavestatus varchar(15) NOT NULL DEFAULT ''  ,
    resultdate timestamp with time zone,
    certificatestatus bool NOT NULL DEFAULT FALSE 
    certificate json NULL)

You miss the DEFAULT for daytype column. Also, you're not catching correctly the errors, because the func leave() can be executed totally before send a response. You can use a return when you find an error.

db, err := sql.Open("postgres", conn)
if err != nil {
    fmt.Println(err.Error())
    e.Data["json"] = err.Error()
    e.ServeJSON()
    return
}

Also, you're creating a connection and not closing it at the end of the func.

Create table in Postgres with json type field using GoLang

1 Answers1