PostgreSQL schemas and search_path

Question

I'm reading Oreilly's PostgreSQL: Up and Running, and in the section Organizing Your Database Using Schemas it says the search_path may be set at the database level:

For example, if we wanted all objects in contrib to be accessible without schema qualification, we would change our database as follows:

ALTER DATABASE mydb SET search_path="$user",public,contrib;

However, after typing the command above, I still can't refer to tables in schemas other then public without qualifying them:

ALTER DATABASE auth SET search_path="$user",public,staging;`
\d users

Gives:

Did not find any relation named "users".

But if I use \d staging.users, it works.

SHOW search_path;

Gives:

   search_path   
----------------
 "$user",public

Does the default search_path from postgresql.conf override the per-database value set with ALTER DATABASE ... SET search_path ? If that's the case, what's the use of the per-database value?

No, I can't select * from users but I can select * from staging.users. — Daniel Serodio
– Daniel Serodio, Commented Aug 10, 2012 at 18:41
Did you reconnect to your database after setting a new default for its search_path? Did you try just setting the search_path for your connection? — kgrittn
– kgrittn, Commented Aug 10, 2012 at 18:46
I hadn't reconnected yet. Reconnecting psql solved my problem, thanks. Would you write an answer so I can mark it as accepted? — Daniel Serodio
– Daniel Serodio, Commented Aug 10, 2012 at 19:39
@Daniel, it's not a syntax error in MySQL (" vs '). Single and double quotes are treated the same (unless it's set on ANSI SQL mode). (Commenting here as you wouldn't see it there. I'll delete this comment later) — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jan 29, 2018 at 23:55

grayhemp · Accepted Answer · 2012-08-15 08:17:22Z

6

The citation from the SET related paragraph of the ALTER DATABASE documentation section is "Whenever a new session is subsequently started in that database, the specified value becomes the session default value". So the changes will take effect for new sessions only. You just need to reconnect.

answered Aug 15, 2012 at 8:17

grayhemp

3971 silver badge5 bronze badges

1

Without reconnecting, to modify search_path on the current session (in your psql session, for example) you can use SET search_path TO "$user",public,contrib;.

Matt
– Matt

2016-05-14 14:59:29 +00:00
Commented May 14, 2016 at 14:59

Add a comment |

Booyakasha · Accepted Answer · 2022-11-20 21:07:36Z

I had the same issue but worked when I altered the user/role with search_path:

demo=> ALTER ROLE bk_test_pg IN DATABASE demo SET search_path TO public, bookings;
ALTER ROLE

demo=> \dt
                List of relations
  Schema  |      Name       | Type  |   Owner    
----------+-----------------+-------+------------
 bookings | aircrafts_data  | table | bk_test_pg
 bookings | airports_data   | table | bk_test_pg
 bookings | boarding_passes | table | bk_test_pg
 bookings | bookings        | table | bk_test_pg
 bookings | flights         | table | bk_test_pg
 bookings | seats           | table | bk_test_pg
 bookings | ticket_flights  | table | bk_test_pg
 bookings | tickets         | table | bk_test_pg
(8 rows)

Before I did for session only and in the new terminal I got same "did not find any relation" error.

demo=> SET search_path TO "$user", public, bookings;
SET
demo=> SHOW search_path;
        search_path        
---------------------------
 "$user", public, bookings
(1 row)

demo=> \dt
                List of relations
  Schema  |      Name       | Type  |   Owner    
----------+-----------------+-------+------------
 bookings | aircrafts_data  | table | bk_test_pg
 bookings | airports_data   | table | bk_test_pg
 bookings | boarding_passes | table | bk_test_pg
 bookings | bookings        | table | bk_test_pg
 bookings | flights         | table | bk_test_pg
 bookings | seats           | table | bk_test_pg
 bookings | ticket_flights  | table | bk_test_pg
 bookings | tickets         | table | bk_test_pg
(8 rows)

So merely altering the search_path with SET search_path TO schema1, schema2, schema3; will only remain in the session.

Stack Exchange Network

PostgreSQL schemas and search_path

2 Answers 2

Your Answer

Hot Network Questions

PostgreSQL schemas and search_path

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions