PostgreSQL | Schema | Configure the Schema Search Path
This page explains how to configure and use the schema search path, which is used to find the schema that contains an object when you specify objects such as tables without a schema name.
What Is the Schema Search Path?
Objects such as tables are created in schemas. Because tables with the same name can exist in different schemas, you must specify schema_name.table_name to identify a table clearly.
For example, if there is a staff table in the schemaA schema and another staff table in the schemaB schema, simply specifying staff does not make it clear which schema’s table is meant.
select * from staff;
To identify the table clearly, specify it as schemaA.staff or schemaB.staff, in the form schema_name.table_name.
select * from schemaA.staff;
Entering the schema name every time is inconvenient, so you can configure which schemas should be used when the schema name is omitted. This setting is the schema search path. Multiple schemas can be configured in the schema search path.
schema_name1, schema_name2 ...
For example, when the schema is omitted and only the table name is written, PostgreSQL checks whether a table with that name exists in each schema from the beginning of the schema search path. When a matching table is found, PostgreSQL treats that schema as the table’s schema.
Now look at how to use and configure the schema search path.
Check the Current Schema Search Path
To check the values configured in the schema search path, run the following.
devkuma=# show search_path;
search_path
-----------------
"$user", public
(1개 행)
devkuma=#
The following value is displayed.
"$user", public
The schema search path separates schemas with commas. "$user" at the beginning represents a schema with the same name as the current role. Because the current connection uses the postgres role, this is equivalent to the following.
postgres, public
How the Schema Search Path Is Used
The schema search path is used a little differently when finding existing tables with SELECT and when creating new tables with CREATE.
First, consider the case of finding a table. When retrieving data from a table, if you omit the schema name and write only the table name, PostgreSQL checks the schemas listed in the schema search path in order from the beginning to see whether the table exists.
For example, assume that the schema search path is as follows.
schemaA, schemaB, schemaC
If you omit the schema and specify only the table name to retrieve data as follows:
select * from mytbl;
PostgreSQL first checks whether mytbl exists in the schemaA schema. If it is found, data is retrieved from schemaA.mytbl. If it is not found, PostgreSQL checks whether mytbl exists in the schemaB schema. This process repeats for the number of schemas configured in the schema search path.
If the table is not found by the end, an error occurs. Schemas not listed in the schema search path are not checked.
–
Next is the case of creating a table. If you omit the schema name and write only the table name, PostgreSQL checks whether the schemas listed in the schema search path exist. When an existing schema is found, the table is created in that schema.
For example, assume that the schema search path is as follows.
schemaA, schemaB, schemaC
PostgreSQL checks from schemaA, and when it finds a schema that actually exists, that schema becomes the current schema.
If you create a table while omitting the schema name as follows, the table is created in the current schema.
create table mytbl (...);
–
Now create a table. You are currently connected to the devkuma database as the superuser postgres role. The schema search path is in its default state, so it is as follows.
"$user", public
The devkuma database does not have a postgres schema with the same name as the role, so the first existing schema in the schema search path is public. Therefore, if you create a table while omitting the schema, the table is created in the public schema.
Run the following to create a table.
devkuma=# create table memo (id integer, memo text);
CREATE TABLE
devkuma=#
The table has been created. To confirm, run the psql meta-command \dt.
devkuma=# \dt
릴레이션(relation) 목록
스키마 | 이름 | 종류 | 소유주
--------+------+--------+----------
public | memo | 테이블 | postgres
(1개 행)
devkuma=#
You can confirm that the memo table just created exists in the public schema.
Change the Schema Search Path
To temporarily change the schema search path only for the current session, use the SET command.
SET search_path TO schema_name1, schema_name2, ...;
For example, to set the schema search path to myschema, public, run the following.
devkuma=# set search_path to myschema, public;
SET
devkuma=#
The schema search path has been changed. To confirm, run show search_path.
devkuma=# show search_path;
search_path
------------------
myschema, public
(1개 행)
devkuma=#
You can confirm that the schema search path has been changed to the specified value.
–
To change the schema search path permanently, use the ALTER ROLE command.
ALTER ROLE user SET search_path = schema_name1, schema_name2 ...;
For example, to set the schema search path to myschema, public, run the following.
devkuma=# alter role postgres set search_path to myschema, public;
ALTER ROLE
devkuma=#
The schema search path has been changed. To confirm, disconnect from PostgreSQL once, connect again, and run show search_path.
devkuma=# alter role postgres set search_path to myschema, public;
ALTER ROLE
devkuma=# \q
C:\Users\kimkc>psql -U postgres
postgres 사용자의 암호:
psql (12.2)
도움말을 보려면 "help"를 입력하십시오.
postgres=# show search_path;
search_path
------------------
myschema, public
(1개 행)
postgres=#
You can confirm that the schema search path has been changed to the specified value.
To restore the default value, run the following.
alter role postgres set search_path to default;
–
To change the default schema search path for all users, edit the PostgreSQL configuration file postgresql.conf. For the location of the postgresql.conf file, see How to Configure the postgresql.conf File.
Open the postgresql.conf file in a text editor and find the following location.
#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
# - Statement Behavior -
#client_min_messages = notice # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# log
# notice
# warning
# error
#search_path = '"$user", public' # schema names
#row_security = on
#default_tablespace = '' # a tablespace name, '' uses the default
#search_path = '"$user", public' # schema names
The current search_path setting is commented out, so remove the first #. Then set the value you want to use as the default schema search path. Here, configure it as follows.
search_path = 'myschema, clientschema' # schema names
After making the change, save the postgresql.conf file and restart PostgreSQL.
Then connect to PostgreSQL and run show search_path.
C:\Users\kimkc>psql -U postgres
postgres 사용자의 암호:
psql (12.2)
도움말을 보려면 "help"를 입력하십시오.
postgres=# show search_path;
search_path
------------------
myschema, clientschema
(1개 행)
postgres=#
You can confirm that the default schema search path has been changed.
–
This page explained how to configure and use the schema search path.