Creating schemas

Creare database PostgreSQL

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
Creare database PostgreSQL

Schema uses

Providing database users with separate environments

Duplicate Schemas for Users

Creare database PostgreSQL

Schemas uses

Organizing database objects into related groups Business Line Schemas

Creare database PostgreSQL

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

Creare database PostgreSQL

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
);
Creare database PostgreSQL

Schema naming restrictions

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

Let's practice!

Creare database PostgreSQL

Preparing Video For Download...