PostgreSQL | Schema | Relationship Between Databases, Schemas, and Tables
PostgreSQL has schemas, which are separate from databases and tables. This page briefly explains the relationship between databases, schemas, and tables in PostgreSQL.
Relationship Between Databases, Schemas, and Tables in PostgreSQL
Here is a brief explanation of the relationship between databases, schemas, and tables in PostgreSQL.
Database
PostgreSQL can manage multiple databases. Immediately after installation, the template databases template0 and template1, and a database named postgres, are created. These databases together are called a database cluster.
If you have permission to create databases, you can add new databases.
Schema
In PostgreSQL, actual data is stored in tables. You can create multiple tables according to purpose, and a database organizes those tables. PostgreSQL also has schemas inside a database.
A schema groups objects such as tables and functions that are created in a database. If schemas are different, tables with the same name can be created in the same database. When you create a database, a special schema named public is created automatically.
By default, the public schema grants privileges and the CREATE privilege to all roles, so any role can create tables in the public schema.
You can create schemas in a database separately from the public schema. Some database systems only allow schemas that have the same name as a user, but PostgreSQL lets you create schemas with any name.
Table
Tables are created in schemas. If schemas are different, tables with the same table name can be created. You can also configure privileges for creating objects such as tables for each schema.
When creating a table with the CREATE TABLE command, if you omit the schema from the table name, the table is created in the public schema by default, unless a schema with the same name as the role exists.
–
This page explained the relationship between databases, tables, and schemas in PostgreSQL.