Change default schema for user

Question

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?

Overlooker

Answer 1

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

Tautologism

On the linked documentation page, I don't see any reference to alter role set search_path to The "exact" syntax is needed, because the grant seems to execute, but the default schema is not changed. – Hermes

Answer 2

In PostGres the exact command would be

ALTER USER 'your-user' set SEARCH_PATH = 'schema_name';

Keheley