More on PostgreSQL schema

The use of schema is not always as explicit as databases and tables in Postgres, so you may not even be aware of them when you first start using it.

When issuing a Postgres command involving tables, the full specification of the table is of the form

database.schema.table

However, the database name must always be the database you're currently connected to, so this is implicit. Thus, once connected to a DB, you can use

schema.table

to refer to a particular table.

Every new database is created with a default schema named public and a utility schema pg_catalog that contains all of the databases's tables, data types, functions, and operators. If no schema is specified when a table is created, it is assumed to belong to the public schema. Postgres includes a "search path" of schema names similar to the $PATH environment variable of GNU/Linux systems such that, if no schema is specified when a table is referenced, Postgres will search through the schema in order until it finds a table with that name. The default ordering is pg_catalog, followed by "$user" and then public, where "$user" is a schema with the name of the current Postgres user.

Thus, if you're using only the default schema, tables can be referred to simply by the table name

table

so that you don't even need to know about the schema sitting between the database and the table.

As a side note, you can see your search path with the psql command

> SHOW search_path; 

(note that pg_catalog is separate from the search path and is checked before it. Unless you've specifically added it to the search path, it will not be shown by the above command). You can see what schema are in a database with the psql command

-- Main.JoelG - 2016-09-20

Comments