Permanently Set Postgresql Schema Path

Question

I need to set schema path in Postgres so that I don t every time specify schema dot table e g  schema2 table   Set schema path   SET SCHEMA PATH a b c   only seems to work for one query session on mac  after I close query window the path variable sets itself back to default    How can I make it permanent

User · Answer

You can set the default search path at the database level   ALTER DATABASE  lt database name gt  SET search path TO schema1 schema2    Or at the user or role level   ALTER ROLE  lt role name gt  SET search path TO schema1 schema2    Or if you have a common default schema in all your databases you could set the system-wide default in the config file with the search path option   When a database is created it is created by default from a hidden  template  database named template1  you could alter that database to specify a new default search path for all databases created in the future  You could also create another template database and use CREATE DATABASE  lt database name gt  TEMPLATE  lt template name gt  to create your databases

User · Answer

And if you have no admin access to the server   ALTER ROLE  lt your login role gt  SET search path TO a b c    Two important things to know about    When a schema name is not simple  it needs to be wrapped in double quotes  The order in which you set default schemas a  b  c matters  as it is also the order in which the schemas will be looked up for tables  So if you have the same table name in more than one schema among the defaults  there will be no ambiguity  the server will always use the table from the first schema you specified for your search path

User · Answer

Josh is correct but he left out one variation   ALTER ROLE  lt role name gt  IN DATABASE  lt db name gt  SET search path TO schema1 schema2    Set the search path for the user  in one particular database

[sql] Permanently Set Postgresql Schema Path

Examples related to sql

Examples related to postgresql