Schema Search Paths in Greenplum

For the database to know in which schema it should look for an object, you can always use the schema-qualified name. For example:

=> SELECT * FROM myschema.mytable;

If you do not want to type the schema-qualified name all the time, you can set the search_path configuration parameter. This tells the database in which order it should search the available schemas for objects. The schema listed first in the search path becomes the default schema. The default schema is where new objects will be created if a schema name is not explicitly declared.

Setting the Schema Search Path

The search_path configuration parameter is used to set the order in which schemas are searched. You can set search_path for a database using the ALTER DATABASE command. For example:

=> ALTER DATABASE mydatabase SET search_path TO myschema, public, pg_catalog;

You can also set search_path for a particular role (user) using the ALTER ROLE command. For example:

=> ALTER ROLE sally SET search_path TO myschema, public, pg_catalog;