Database object name resolution using the search path - PolarDB - Alibaba Cloud - PolarDB

In an SPL program, every database object reference is either a qualified name or an unqualified name.

Qualified name — schema.name. Because the schema is explicit, the reference is unambiguous: the object either exists in that schema or it does not.
Unqualified name — name only, without a schema. prefix. The database resolves it by searching the current user's search path.

How unqualified name resolution works

When you reference an unqualified name, PolarDB searches a list of schemas called the search path. When a user becomes the current user of a session, the default search path is always associated with that user. Schemas are searched from left to right. If no matching object is found in any schema in the list, the reference fails.

Run the following command in psql to view the current search path:

SHOW search_path;

Example output:

   search_path
-----------------
 "$user", public
(1 row)

The $user entry is a placeholder for the current session user. If the current user is polardb, PolarDB searches the polardb schema first, then the public schema.

Once an unqualified name resolves to a specific object, PolarDB checks whether the current user has the required privilege to perform the requested operation on that object.

Oracle compatibility note

The search path concept does not exist in Oracle. In Oracle, an unqualified reference resolves only to the current user's own schema, because a user and a schema are the same entity — for example, the hr user owns the hr schema, and hr.employees refers to the employees table in that schema. Qualified names in Oracle therefore follow the pattern username.object_name.

In PolarDB for PostgreSQL(Compatible with Oracle), a user and a schema are two separate objects. A user named hr does not automatically have a schema named hr. To replicate Oracle-style name resolution in PolarDB for PostgreSQL(Compatible with Oracle), create a dedicated schema for each user and set the search path accordingly.