Understanding Schemas

Wednesday, 08 October 2014

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 schemas or namespaces

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    |
    "messages_pkey" PRIMARY KEY, btree (id)

test=# \d messages
   Table "public.messages"
 Column |  Type   | Modifiers
 id     | integer | not null
 data   | text    |
 date   | date    |
    "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;

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);
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.