Creating schemas

Creating PostgreSQL Databases

Darryl Reeves

Industry Assistant Professor, New York University

PostgreSQL schemas

  • A named container for tables

Tables in Schema

  • Can contain objects such as data types and functions
1 https://www.postgresql.org/docs/9.1/ddl-schemas.html
Creating PostgreSQL Databases

Schema uses

Providing database users with separate environments

Duplicate Schemas for Users

Creating PostgreSQL Databases

Schemas uses

Organizing database objects into related groups Business Line Schemas

Creating PostgreSQL Databases

The default schema

  • The public schema is the default schema in PostgreSQL
CREATE TABLE topic (
    id serial PRIMARY KEY,
    description TEXT NOT NULL
);

public.topic

Creating PostgreSQL Databases

The CREATE SCHEMA command

CREATE SCHEMA schema_name;

CREATE SCHEMA division1;

CREATE TABLE division1.school (
    id serial PRIMARY KEY,
    name TEXT NOT NULL,
    mascot_name TEXT,
    num_scholarships INTEGER DEFAULT 0
);
Creating PostgreSQL Databases

Schema naming restrictions

  • Length of name less than 32
  • Name begins with letter or underscore ("_")
  • Schema name cannot begin with "pg_"
Creating PostgreSQL Databases

Let's practice!

Creating PostgreSQL Databases

Preparing Video For Download...