PostgreSQL - ALTER SCHEMA

In PostgreSQL, schemas are a way to logically group tables, views, functions, and other database objects. The ALTER SCHEMA command allows you to modify the properties of an existing schema.

Here are some common uses of the ALTER SCHEMA command:

1. Rename a Schema:

To rename an existing schema:

ALTER SCHEMA old_schema_name RENAME TO new_schema_name;

2. Change the Owner of a Schema:

To change the owner of a schema:

ALTER SCHEMA schema_name OWNER TO new_owner;

Points to Remember:

Only the schema's owner, a superuser, or a role with the CREATEDB privilege can rename a schema.
Only the schema's owner or a superuser can change the owner of a schema.
Be cautious when renaming schemas or changing schema ownership, especially in production environments. Such changes can affect the routines and applications that depend on specific schema names or ownership.
It's a good practice to perform schema changes during maintenance windows or downtimes to ensure that dependent applications or services aren't affected during the process.
Always take backups before making changes and thoroughly test any changes in a development or staging environment before applying them in production.

Moving tables to a different schema in PostgreSQL:
- Description: Transferring tables from one schema to another in PostgreSQL.
- Code Example:
```
ALTER TABLE your_schema.old_table SET SCHEMA new_schema;
```
Changing the owner of a schema in PostgreSQL:
- Description: Modifying the owner of an existing PostgreSQL schema.
- Code Example:
```
ALTER SCHEMA your_schema OWNER TO new_owner;
```
Altering the default privileges for a schema in PostgreSQL:
- Description: Adjusting default privileges for objects created in a schema.
- Code Example:
```
ALTER DEFAULT PRIVILEGES IN SCHEMA your_schema GRANT SELECT ON TABLES TO new_role;
```

Adding or removing a schema in PostgreSQL:

Description: Creating or deleting a schema in PostgreSQL.

Code Examples:

-- Creating a new schema
CREATE SCHEMA new_schema;

-- Removing an existing schema
DROP SCHEMA existing_schema;

PostgreSQL ALTER SCHEMA vs ALTER TABLE:
- Description: Distinguishing between modifying a schema and modifying a table.
- Code Example:
```
-- ALTER SCHEMA
ALTER SCHEMA your_schema OWNER TO new_owner;

-- ALTER TABLE
ALTER TABLE your_table SET SCHEMA new_schema;
```
Modifying schema attributes in PostgreSQL:
- Description: Adjusting various attributes of a PostgreSQL schema.
- Code Example:
```
ALTER SCHEMA your_schema SET attribute_name = attribute_value;
```
Renaming a schema in PostgreSQL:
- Description: Changing the name of an existing PostgreSQL schema.
- Code Example:
```
ALTER SCHEMA old_schema RENAME TO new_schema;
```

Managing schema permissions with ALTER SCHEMA:

Description: Adding or removing permissions for a specific schema.

Code Examples:

-- Granting USAGE privilege on a schema
ALTER SCHEMA your_schema GRANT USAGE ON SCHEMA TO new_role;

-- Revoking CREATE privilege on a schema
ALTER SCHEMA your_schema REVOKE CREATE ON SCHEMA FROM another_role;