How to Create a Schema in PostgreSQL

Schemas are a great way to organize data in a PostgreSQL database. A schema allows you to group database objects such as tables, views, indexes and more into logical namespaces. This can help keep a complex database well structured.

In this comprehensive guide, we‘ll cover everything you need to know about creating schemas in PostgreSQL, including:

• What are schemas and why are they useful?
• How to create a schema
• Setting the schema owner
• Creating a schema if it doesn‘t exist
• Search path and current schema
• Creating objects inside schemas
• Altering schemas
• Dropping schemas
• Example use cases

What are PostgreSQL Schemas?

A schema is essentially a namespace that contains named database objects such as tables, views, indexes and more. Schema names begin with a letter and can contain letters, numbers and underscores.

Some key things to know about schemas:

• Schemas group together related database objects.
• You can only have one schema per database.
• Objects with the same name can exist in different schemas without conflict.
• The public schema is the default and is present in all databases.

Why Use Schemas?

Here are some of the main benefits of using schemas:

Logical grouping – As mentioned schemas allow you to group related objects. For example you may group forum tables under a forum schema.

Access control – Permissions can be granted on per schema basis, allowing restrictions on who can access what data.

Namespacing – Identically named objects can exist across schemas avoiding naming collisions. For example both forum and blog schemas can have a posts table.

In summary, schemas make it easier to manage permissions, organize related data, and avoid naming conflicts in large and complex databases.

Creating a Schema in PostgreSQL

The syntax to create a schema in SQL is:

CREATE SCHEMA schema_name;

For example to create a blog schema:

CREATE SCHEMA blog;

Let‘s look at some examples of creating schemas in PostgreSQL.

Create Schema Example

To demonstrate, we‘ll connect to a test database and create a simple blog schema:

postgres=# CREATE DATABASE testdb;
CREATE DATABASE
postgres=# \c testdb

testdb=# CREATE SCHEMA blog;
CREATE SCHEMA

And to verify it exists we can query the information_schema meta-data:

testdb=# SELECT schema_name FROM information_schema.schemata WHERE schema_name = ‘blog‘;
 schema_name
-------------
 blog
(1 row)

Setting Schema Owner

When a schema is created it will be owned by the user that created it.

You can also specify the owner using the AUTHORIZATION clause:

CREATE SCHEMA blog AUTHORIZATION someuser;

If the specified user doesn‘t exist you‘ll get an error.

Setting an owner allows you to later revoke rights and control who has access. By default the owner has full CREATE and USAGE rights on the schema.

Create Schema IF NOT EXISTS

It can be helpful to avoid errors by using IF NOT EXISTS when creating a schema. This will produce a notice instead of failing if it already exists:

testdb=# CREATE SCHEMA IF NOT EXISTS blog;
NOTICE:  schema "blog" already exists, skipping

Current Schema vs Search Path

There are two important concepts that impact how schemas are accessed:

1. The current schema
2. The search path

The current schema is the default schema used for naming resolution if a schema is not specified.

You can view your current schema using:

testdb=# SHOW search_path;

search_path
-----------------
 "$user", public
(1 row)

This brings us to the search path. The search path is an ordered list of schemas that are checked when an object is referenced without a schema prefix.

From our example above:

• First the $user schema is checked (this will match the current user name)
• Then public is checked

You can set the search path as needed:

SET search_path TO blog, public;

Generally you will want public present so all users can access the standard shared schema.

Now let‘s look at creating objects inside schemas.

Creating Objects in a Schema

Schemas themselves don‘t do much until you add objects like tables. The syntax for creating an object in a schema is:

schema.object

If no schema is specified then the current schema is used.

Let‘s create a posts table in our blog schema example:

testdb=# CREATE TABLE blog.posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT
);

CREATE TABLE

Then to query the table, we need to reference the schema prefix blog.:

testdb=# SELECT * FROM blog.posts;
 id | title | content 
----+-------+---------
(0 rows)

This keeps things properly namespaced.

You can save some typing by setting the search path to include blog first. Then you can omit the schema prefix:

testdb=# SET search_path TO blog, public;
SET
testdb=# SELECT * FROM posts;
 id | title | content
----+-------+---------
(0 rows) 

Just keep in mind that mostly fully qualified names are considered better practice to avoid confusion.

Let‘s look at a couple more examples of creating objects inside schemas:

Create Table in Schema

Adding a table uses standard SQL syntax, specifying the schema name first:

CREATE TABLE schema.table (
   ...
);

For example:

CREATE TABLE blog.articles (
   id SERIAL PRIMARY KEY,
   title TEXT NOT NULL,
   content TEXT
);

Create View in Schema

Much like tables, you can create views inside schemas:

CREATE VIEW blog.popular_articles AS
SELECT * 
FROM blog.articles
WHERE views > 1000;

Create Function in Schema

Stored procedures, functions and other logic can also be namespaced by schema:

CREATE FUNCTION blog.get_article(id int) 
RETURNS text AS $$
   ...
$$ LANGUAGE sql;

This keeps application logic organized as well.

Now that we have some objects created, let‘s look at managing schemas.

Altering Schemas

Occasionally you may need to modify a schema. Some common schema alterations include:

Rename Schema

The syntax to rename an existing schema is:

ALTER SCHEMA name RENAME TO new_name; 

For example to rename blog to cms:

ALTER SCHEMA blog RENAME TO cms;

Any objects that reference the original schema name will need to be updated.

Change Schema Owner

To change the owner and transfer ownership of a schema:

ALTER SCHEMA name OWNER TO new_owner;

For example to set the owner of the cms schema to john:

ALTER SCHEMA cms OWNER TO john;

Now user john controls the schema and contained objects.

Let‘s look at removing schemas as well.

Dropping Schemas

To drop or remove a schema you would use:

DROP SCHEMA name;

However, PostgreSQL does not allow you to remove a schema that contains objects. You will get an error like:

ERROR:  cannot drop schema cms because other objects depend on it
DETAIL:  table cms.articles depends on schema cms
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

To overcome this you need to either:

1. Manually drop all objects first
2. Use CASCADE to auto drop

Here is an example using CASCADE which will remove all containing objects:

DROP SCHEMA cms CASCADE;

And verification no longer exists:

SELECT schema_name FROM information_schema.schemata WHERE schema_name = ‘cms‘;

 schema_name
-------------
(0 rows)

So in summary to delete a schema:

• Manually drop objects first
• Use DROP SCHEMA ... CASCADE

PostgreSQL Schema Use Cases

Now that we have covered the basics of working with schemas let‘s look at some common use cases.

Schemas can help in many ways from access control to code organization and more.

Application Organization

Schemas make it very easy to group all objects for an application feature set.

For example if building a content management system (CMS) you may structure it as:

• cms schema
  • articles table
  • categories table
  • Article triggers, functions, etc

Keeping all CMS related logic under a single schema namespace separates it from other application database objects.

Multi-Tenant Apps

Schemas can be very useful for multi-tenant software where a single app serves multiple customers.

In this model you may create a separate schema per customer or organization. For example:

• customer1
• customer2
• customer3

Then all core app tables like users, accounts, settings are created per schema. This avoids namespace collisions between nearly identically named entities. It also ensures customer data isolation from each other.

Legacy App Migrations

When migrating or upgrading legacy applications, the existing database objects can be moved into a separate legacy schema before being upgraded.

This provides easy access to the old dataset during migration phases. And avoids naming collisions with new versions of tables using the same identifiers.

Schema Migrations

In practice database changes are often done via schema migrations in a version control system. This allows safely applying incremental changes as code.

Migrations for schemas themselves can involve:

• Creating / dropping schemas
• Moving objects between schemas
• Renaming schemas

And schema migrations can be done alongside regular table / data migrations.

Permission Control

Wrapping application objects under a schema is a great way to manage database permissions.

Rather than granting direct table access, permissions can be granted on a per schema basis.

For example a app_read_only role can be granted usage on an app schema. This only allows visibility of the objects within that schema without altering data.

More granular rights can also be assigned, like allowing access to certain tables or columns as needed.

Conclusion

PostgreSQL schemas provide useful logical grouping and namespaces for database objects.

Some key points about schemas:

• Schemas group tables, views and other objects
• Sets of objects can be created inside schemas
• The same object names can exist in different schemas
• The default public schema always exists
• Schemas must be empty before dropping

In this guide we covered how to:

• Create and manage schemas
• Set a schema owner
• Create various objects within schemas
• Use schemas for permissions and organization

So in summary schemas are a very useful construct in SQL that helps avoid name collisions and better structure databases. They aid organization and access control for complex systems and applications.