How to Create Schema in PostgreSQL: Syntax and Examples

In PostgreSQL, you create a schema using the CREATE SCHEMA statement followed by the schema name. This organizes database objects like tables and views under a named group for better management.

Syntax

The basic syntax to create a schema in PostgreSQL is:

• CREATE SCHEMA schema_name; creates a new schema with the specified name.
• You can optionally specify an AUTHORIZATION clause to assign ownership to a specific user.

sql

CREATE SCHEMA schema_name;

-- Or with ownership
CREATE SCHEMA schema_name AUTHORIZATION user_name;

Example

This example creates a schema named sales owned by the user alice. It shows how to organize tables under this schema.

sql

CREATE SCHEMA sales AUTHORIZATION alice;

CREATE TABLE sales.orders (
  order_id SERIAL PRIMARY KEY,
  order_date DATE NOT NULL,
  amount NUMERIC(10,2) NOT NULL
);

SELECT schema_name FROM information_schema.schemata WHERE schema_name = 'sales';

Output

 schema_name 
-------------
 sales
(1 row)

Common Pitfalls

Common mistakes when creating schemas include:

• Trying to create a schema that already exists without using IF NOT EXISTS.
• Not having the right permissions to create a schema or assign ownership.
• Forgetting to qualify table names with the schema name when accessing objects.

Example of wrong and right usage:

sql

-- Wrong: creating existing schema without check
CREATE SCHEMA sales;

-- Right: avoid error if schema exists
CREATE SCHEMA IF NOT EXISTS sales;

Quick Reference

Command	Description
CREATE SCHEMA schema_name;	Create a new schema with default owner.
CREATE SCHEMA schema_name AUTHORIZATION user_name;	Create schema owned by specific user.
CREATE SCHEMA IF NOT EXISTS schema_name;	Create schema only if it does not exist.
DROP SCHEMA schema_name;	Remove a schema and all its objects.
SET search_path TO schema_name;	Set default schema for object lookup.

Key Takeaways

Use CREATE SCHEMA schema_name; to create a new schema in PostgreSQL.

Assign ownership with AUTHORIZATION user_name to control schema access.

Use IF NOT EXISTS to avoid errors when creating schemas that may already exist.

Remember to qualify table names with the schema name or set the search path.

Ensure you have proper permissions before creating or modifying schemas.