sqlpostgresqlpostgresql-9.5

Change default schema for user

create schema bla;

-- then create table table_name into this schema

Then I want change default schema for user (user is postgres)

I do: ALTER ROLE postgres SET search_path TO bla; (Query returned successfully with no result).

When I try SELECT * FROM table_name gives error relation "table_name" does not exist

Though SELECT * FROM bla.table_name works fine.

What is wrong in my attempt to change default schema for user?

Solution

  • I think you need to relogin for that. With ALTER USER ... SET you change

    Session defaults for run-time configuration variables

    Also from ALTER ROLE SET manual:

    Role-specific variable settings take effect only at login;

    But don't apply changes to current session. If you want immediate change use: 

    SET search_path TO bla;

    It will change path on session level