16

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

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
Abhishek Soni
  • 1,677
  • 4
  • 20
  • 27
  • 1
    jdbc is java, not go – Woot4Moo Jul 21 '18 at 19:05
  • @Woot4Moo, that I know that's why I mentioned *as per doc*; sorry if it was not clear, but I have mentioned the code snippet below I tried adding those `currentSchema=myschema` to the statement but its not working – Abhishek Soni Jul 21 '18 at 19:15

3 Answers3

11

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 ``+``

Mytholog
  • 146
  • 5
4

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`)
...
Cetin Basoz
  • 22,495
  • 3
  • 31
  • 39
  • I am using ` db, err := sql.Open("postgres", `dbname=`+dbName+` user=`+username+` password=`+password+` host=`+host+` sslmode=disable`) if err != nil { panic("Main db connection failed") } db.SetMaxIdleConns(40) db.SetMaxOpenConns(160) ` So there are many parallel connections, I am doubtful or can you explain me will executing this `db.Exec(`set search_path='mySchema'`)` will set schema for every connection? – Abhishek Soni Jul 22 '18 at 08:53
  • @AbhishekSoni you are writing what I did in a different way. That exec is needed once per connection. Like you need to Open() per connection you need to Exec per db once. – Cetin Basoz Jul 22 '18 at 12:12
  • I am confused with the functionality of connections but indeed wanted to know that `db.Exec(`set search_path='mySchema'`)` is required **once** per *OPEN* and not per connection (as in example i am setting maxConns to 40 so need to execute it 40 times)? – Abhishek Soni Jul 23 '18 at 09:38
  • Why wouldn't you simply test and see? – Cetin Basoz Jul 23 '18 at 09:47
  • @AbhishekSoni you should use a transaction – Ilia Choly Feb 20 '19 at 19:07
-2

Set Search_path didn’t work for me.

nator
  • 1
  • Rather setting search_path=myschema in the dsn (connection string) worked perfectly – nator Jul 17 '23 at 14:03
  • 1
    This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/34703366) – JimB Jul 18 '23 at 17:34