How to Set Search Path in PostgreSQL: Syntax and Examples

In PostgreSQL, you set the schema search order using the SET search_path command. This controls which schemas PostgreSQL looks in first when you refer to database objects without schema qualification. For example, SET search_path TO schema1, schema2; sets the search order to schema1 first, then schema2.

Syntax

The SET search_path command defines the order of schemas PostgreSQL searches for tables, functions, and other objects when no schema is specified.

• search_path: The list of schemas to search, separated by commas.
• TO: Keyword to assign the new search path.
• Schemas are searched in the order listed, from left to right.

sql

SET search_path TO schema1, schema2, "$user", public;

Example

This example sets the search path to sales first, then public. When you query a table without schema prefix, PostgreSQL looks in sales first.

sql

CREATE SCHEMA sales;
CREATE TABLE sales.customers (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE public.customers (id SERIAL PRIMARY KEY, name TEXT);

SET search_path TO sales, public;

-- This query finds the customers table in sales schema
SELECT * FROM customers;

Output

 id | name 
----+------
(0 rows)

Common Pitfalls

Common mistakes when setting the search path include:

• Not including public schema if your tables are there, causing "relation does not exist" errors.
• Assuming the search path change is permanent; it lasts only for the current session unless set in configuration.
• Using incorrect schema names or forgetting to quote names with uppercase or special characters.

sql

/* Wrong: missing public schema */
SET search_path TO sales;
-- May cause errors if tables are in public schema

/* Right: include public schema */
SET search_path TO sales, public;

Quick Reference

Command	Description
SET search_path TO schema1, schema2;	Set search order of schemas for current session
SHOW search_path;	Display current search path setting
ALTER ROLE username SET search_path TO schema1, public;	Set default search path for a user
ALTER DATABASE dbname SET search_path TO schema1, public;	Set default search path for a database

Key Takeaways

Use SET search_path TO schema1, schema2; to control schema lookup order in PostgreSQL.

Always include public schema if your tables reside there to avoid missing table errors.

Search path changes apply only to the current session unless set permanently via role or database settings.

Check current search path anytime with SHOW search_path;.

Schema names with uppercase or special characters must be quoted in the search path.