Schemas

What Are Schemas in PostgreSQL?

A given Postgres database is organized with at least one schema.

A schema logically groups database objects such as tables for manageability and access constraining.

Two or more tables with the same name can exist within the same database as long as they are grouped in different schemas.

At any given time a client can be connected to only one database but access objects in many different schemas within that database as long as it has the respective role privileges to that objects.

To create a schema within a currently connected database use CREATE SCHEMA commands. To drop use DROP SCHEMA command.

CREATE SCHEMA schema_name;

An object contained within a given schema is indicated using the dot notation: schema_name.schema_object_name.

Public Schema

Each Postgres database has a schema called public. When an object is specified without schema indication the public schema is indicated by default. By default the command CREATE TABLE users (); and the command CREATE TABLE public.users (); are tantamount. However, the default schema can be changed.

SQL Standard & Other SQL Standard Implementations

Schema rules vary between SQL standard, Postgres implementation and other SQL standard implementations. If portability is an issue use Postgres schema system with caution.