SQL “schemas”
Author: Diane Horton
Schema: a kind of namespace
• “psql csc343h-dianeh” connects you to a database called csc343h-dianeh.
(Substitute your cdf userid of course.)
• Everything defined (tables, types, etc.) goes into one big pot.
• Schemas let you create different namespaces.
• Useful for logical organization, and for avoiding name clashes.
2
Creating a schema
• You already have a schema called “public”. • You can also create your own. Example:
create schema University;
• To refer to things inside a particular schema, you can use dot notation:
create table University.Student (…);
select * from University.Student;
3
If you don’t use dot notation
• If you refer to a name without specifying what schema it is within:
• Any new names you define go in the schema called “public”
• E.g., if you create a table called frindle, you actually are defining public.frindle.
• When referring to a name, there is a search path that finds it.
4
The search path
• To see it the search path: show search_path;
• You can set the search path yourself. Example: set search_path to University, public;
• The default search path is: “$user”, public
• schema “$user” is not created for you, but if you create it, it’s at the front of the search path.
• schema public is created for you.
5
Removing a schema
• Easy:
drop schema University cascade;
• “cascade” means everything inside it is dropped
too.
• To avoid getting an error message if the schema does not exist, add “if exists”.
6
Usage pattern
• You can use this at the top of every DDL file:
drop schema if exists University cascade;
create schema University;
set search_path to University;
• Helpful during development, when you may want to change the schema, or test queries under different conditions.
7
Workflow
• One effective way to work:
• Create a DDL file with the schema.
• Create a file with inserts to put content in the database.
• In the postgreSQL shell, import these.
• Run queries directly in the shell or by importing queries written in files.
8