Table of Contents
I've been brushing up on my SQL lately, and one key concept that I forgot about where schemas. I rarely used them since I created most of my tables in the public schema (shame on me), but it was interesting to read up the documentation on them again.
Schemas are namespaces
In short, schemas are namespaces that house your functions, tables triggers etc. What's a namespace? Its like a container of addresses. The reason why we use them is to prevent cluttering of the main/public namespace.
In fact, every table that you've ever created inside postgres lives inside a schema. For example, in psql you can use \d and then hitting tab to show you what schemas are available:
The dots indicate that those options are schemas. There is always a schema involved when you create a table or a function. Generally, all your functions are put into the public schema. So, in the above example, we can see messages as a table, and it looks as though its not in a schema, but it is. One can also refer to it as public.messages:
test=# \d public.messages
Table "public.messages"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
data | text |
date | date |
Indexes:
"messages_pkey" PRIMARY KEY, btree (id)
test=# \d messages
Table "public.messages"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
data | text |
date | date |
Indexes:
"messages_pkey" PRIMARY KEY, btree (id)
Inside a Schema
To see what tables are inside of a schema, you can keep on using tab completion, use a wildcard or use a query. Tab completion is pretty self-explanatory. If you want to use a wildcard then use it like so:
exercises=# \dt cd.*
List of relations
Schema | Name | Type | Owner
--------+------------+-------+------------------
cd | bookings | table | quazinafiulislam
cd | facilities | table | quazinafiulislam
cd | members | table | quazinafiulislam
(3 rows)
We use the extra t in \dt to give us table information only. If you use used \d cd.*, then you'd get a lot more information regarding each table. You can try it out for yourself.
Another way to get the same information is use to use a query:
SELECT * FROM information_schema.tables WHERE table_schema = 'cd'
Note that this is also a schema, and this schema also exists under the public schema. What do I mean by that, well take a look at this:
exercises=# \d public.information_schema.tables
View "information_schema.tables"
Column | Type | Modifiers
------------------------------+-----------------------------------+-----------
table_catalog | information_schema.sql_identifier |
table_schema | information_schema.sql_identifier |
table_name | information_schema.sql_identifier |
table_type | information_schema.character_data |
self_referencing_column_name | information_schema.sql_identifier |
reference_generation | information_schema.character_data |
user_defined_type_catalog | information_schema.sql_identifier |
user_defined_type_schema | information_schema.sql_identifier |
user_defined_type_name | information_schema.sql_identifier |
is_insertable_into | information_schema.yes_or_no |
is_typed | information_schema.yes_or_no |
commit_action | information_schema.character_data |
In essence, everything lives under the public schema.
Creating Schemas
Creating schemas are as simple as creating tables. Here's an example:
test=# CREATE SCHEMA happy_schema;
CREATE SCHEMA
When you create a schema, you can also create tables and functions under it in one command:
test=# CREATE SCHEMA ShoppingCenters
test-# CREATE TABLE Malls (id integer PRIMARY KEY, name VARCHAR(100))
test-# CREATE TABLE SuperMarkets (id integer PRIMARY KEY, name VARCHAR(100), capacity integer);
CREATE SCHEMA
test=# \dt shoppingcenters.*
List of relations
Schema | Name | Type | Owner
-----------------+--------------+-------+------------------
shoppingcenters | malls | table | quazinafiulislam
shoppingcenters | supermarkets | table | quazinafiulislam
(2 rows)
This feels a lot like using python's with statement.