What are database schema in PostgreSQL?
Overview
In a database, a schema is an object container. Objects can be tables, data types, functions, or operators. There are many reasons for using schemas in a database cluster. Let's see some of them.
- Schemas allow many users to use one database without interfering with each other.
- Schemas organize database objects into logical groups to make them more manageable.
Note: A schema can be compared to a directory (at the OS level) except that it cannot be nested. It can also be seen as a namespace.
Working with schema
Every new database contains a default schema called public. To see it, use the command below:
\dn;
When we create a table (CREATE TABLE myTable(...)), we create it in the public schema (CREATE TABLE public.myTable(...)).
Basic operations
Let's see how to create a new schema. The syntax looks like this:
CREATE SCHEMA schema_name;
Next, let's see how to delete a schema. There are two options to consider here: empty schema (no object in the schema) and non-empty schema. If it is empty, we use DROP like this:
DROP SCHEMA schema_name;
In case the schema is not empty, we add CASCADE at the end of the syntax above:
CREATE SCHEMA schema_name CASCADE;
Exercises
Let's create new schemas below.
-- create two schemas for universitiesCREATE SCHEMA unigom;CREATE SCHEMA unikis;-- view schemas\dn;
- Line 1–2: We create
unigomandunikisschemas. - Line 6: We list all available schemas.
Next, let's create tables in each of those schemas.
CREATE TABLE unigom.student (id INT NOT NULL PRIMARY KEY,name VARCHAR(45),department VARCHAR(40),startDate DATE);CREATE TABLE unikis.lecture (id INT NOT NULL PRIMARY KEY,name VARCHAR(45),speciality VARCHAR(40),startDate DATE);\dt unigom.*;\dt unikis.*;
Using qualified name, we create the student table in unigom schema (line 1) and the lecture table in unikis schema (line 8).
Note: A qualified name consists of the schema name and table name separated by a dot.
Quiz
In the widget below, create the lecture table in unigom schema and the student table in the public schema.
-- your code goes here...
Let's move the student table from the public schema to unikis.
ALTER TABLE studentSET SCHEMA unikis;-- list tables in unikis schema\dt unikis.*
Wrap up
Just like we have directories in our system to organize files, schemas let us organize tables in a database. The following are the main points to remember:
CREATE SCHEMA/DROP SCHEMA: Create a new schema / remove a schema.- Use object qualified name to write or access an object in a given schema.
ALTER TABLE ... SET SCHEMA ...: Move a table to another schema.
