Schemas in PostgreSQL

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.

We use cookies and similar technologies to enhance the quality of services, maintain statistics and adjust marketing content. You will find more information in the Cookies Policy.

By clicking OK you grant consent to processing of your personal data by us and our Trusted Partners with the purpose of maintain statistics and adjustment of the marketing content pursuant to the Privacy Policy. If you wish to not grant that consent and/or limit its extent click Settings.