doukeyong3746487
doukeyong3746487
2018-07-21 18:54
浏览 254

Golang:使用PostgreSQL模式进行连接

I am looking to connect and query to a PostgreSQL. But I only want to connect to a particular Schema.

As per the doc (JDBC) we can use

jdbc:postgresql://localhost:5432/mydatabase?searchpath=myschema

or update As of 9.4 you can specify the url with the new currentSchema parameter like so:

jdbc:postgresql://localhost:5432/mydatabase?currentSchema=myschema

But I am unable to do so with golang SQL driver;

As per the documents, we can also use SET search_path TO myschema,public; But I only want to declare it for once during initializing but I think this needs to be executed every time for new connection.

Also I am using following code please help me identify the correct parameters to be passed to this in order to only connect with schema

db, err := sql.Open("postgres", `dbname=`+s.settings.Database+
` user=`+s.settings.Username+` password=`+s.settings.Password+
` host=`+s.settings.Url+` sslmode=disable`) 

Adding currentSchema=myschema or searchpath=myschema is not working!

Is there a way I can only connect to a particular database-schema in GO

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dongxu1668
    dongxu1668 2018-07-23 15:47
    已采纳

    You should add search_path=myschema to dataSourceName

    P.S. better use fmt.Sprintf("host=%s port=%d dbname=%s user=%s password='%s' sslmode=disable search_path=%s", ...) instead ``+``

    点赞 评论
  • douchen9569
    douchen9569 2018-07-21 20:23

    Set Search_path is right and you do it once. ie:

    db, err := sql.Open("postgres",
        "host=localhost dbname=Test sslmode=disable user=postgres password=secret")
    if err != nil {
       log.Fatal("cannot connect ...")
    }
    defer db.Close()
    db.Exec(`set search_path='mySchema'`)
    
    rows, err := db.Query(`select blah,blah2 from myTable`)
    ...
    
    点赞 评论

相关推荐