Setting search_path for a postgres resource connection

Queries and Resources
1

  • Goal: set search_path for a Postgres resource connection so that I can nominate a 'test' schema in place of the 'public' schema

  • Steps: I've tried using Connection options by setting the key to 'search_path' and the value to 'test'.

  • Details: Instead of having to write "SELECT * FROM test.table_name" I want to change the search_path to 'test' so that I can write "SELECT * FROM table_name" and get the same results. Ideally, I want to be able to change the schema name in the connection resource so that I don't need to update all my queries to change the tables they are pointing to. For example, if I change the search_path to 'test2' then "SELECT * FROM table_name" will automatically query the data in test2.table_name. The solution proposed Setting search_path for Postgres Resources doesn't work because it involves setting the search_path for in each individual query, which is what I'm trying to avoid.

  • The error I get is: * message:"relation "table_name" does not exist"

  • Screenshots: here's the relevant settings in

    image
    image1126×330 11.8 KB

2

I think you already check the existing post, Setting search_path for Postgres Resources, which shows the connection settings doesn't work. Actually the set search_path could work in different levels, that post solution is the query level. You could use the user or database level like below:

# user level
ALTER USER your_username SET search_path TO schema1, public;

# database level
ALTER DATABASE your_database_name SET search_path TO schema1, schema2, public;

Of course you have to run this query out of retool, such as the psql. Then you can use the normal retool connection settings.

1 Like
3

I can see the ALTER USER option giving me the functionality I'm after. I'll try that out. Thanks!!!

4

It worked!!

1 Like