PostgreSQL Schema Operations: Add, Alter, and Delete

Overview

Managing schemas is essential for database structuring in PostgreSQL. This tutorial covers the essential schema operations: adding, altering, and deleting, with step-by-step code examples.

Introduction to Schemas

In PostgreSQL, a schema is a namespace that contains database objects like tables, views, indexes, and functions. Schemas allow you to organize your database objects into logical groups, making them easier to manage and access. By default, every PostgreSQL database contains a schema named ‘public,’ but creating additional schemas can help avoid naming conflicts and provide control over access to database objects.

Adding a New Schema

CREATE SCHEMA my_new_schema;

To create a new schema, use the CREATE SCHEMA command followed by your chosen schema name. This will add a new schema to your database under which you can create and organize database objects.

Altering a Schema

A common task is renaming an existing schema:

ALTER SCHEMA old_schema_name RENAME TO new_schema_name;

Another common operation is transferring ownership of a schema to a different user:

ALTER SCHEMA my_schema OWNER TO new_owner;

Deleting a Schema

To drop a schema, you have two options: use DROP SCHEMA for an empty schema, or use DROP SCHEMA ... CASCADE to drop the schema and all contained objects.

DROP SCHEMA my_old_schema;
DROP SCHEMA my_old_schema CASCADE;

The first command will fail if the schema contains any objects. The second variant will delete all objects within the schema as well.

Working with Tables within Schemas

Creating Tables

CREATE TABLE my_new_schema.my_table (
  id SERIAL PRIMARY KEY,
  data VARCHAR(100)
);

This query creates a new table named my_table within my_new_schema. The table has a primary key column id and a data column that can contain strings up to 100 characters.

Altering Tables

ALTER TABLE my_new_schema.my_table
ADD COLUMN extra_data INT;

This modifies my_table by adding a new integer column named extra_data.

Deleting Tables

DROP TABLE my_new_schema.my_table;

This command will drop the table named my_table from the schema my_new_schema.

Advanced Schema Operations

Setting a Default Schema

SET search_path TO my_new_schema, public;

The search_path setting determines which schemas the database should search when an unqualified object name is used. With the above command, PostgreSQL will look in my_new_schema first, then in public, when resolving object names.

Moving Objects Between Schemas

ALTER TABLE public.my_table SET SCHEMA my_new_schema;

This code example demonstrates how to move my_table from the public schema to my_new_schema.

Granting and Revoking Access to Schemas

GRANT USAGE ON SCHEMA my_new_schema TO user_name;
REVOKE USAGE ON SCHEMA my_new_schema FROM user_name;

Granting USAGE on a schema gives a user access to objects within it. Revoking USAGE removes this access.

Conclusion

Mastering schema operations in PostgreSQL enhances your ability to organize and secure your data effectively. This guide has introduced foundational and advanced schema management techniques that should empower your database handling strategies as you work with PostgreSQL.