PostgreSQL - Schema

In PostgreSQL, a schema is essentially a namespace that allows multiple sets of tables, views, and other objects to coexist within the same database. A schema can contain tables, views, sequences, indexes, data types, functions, operators, and aggregates. The use of schemas can be particularly beneficial for organizing objects, managing permissions, and enabling multiple users or applications to use a single database without interfering with one another.

Key Characteristics of Schemas:

Namespacing: A database can contain multiple schemas, and each schema can contain tables with the same name. For instance, both schema1.mytable and schema2.mytable can coexist in the same database.
Security: Permissions can be set at the schema level. For example, a user could be granted access to only specific schemas within a database.
Search Path: PostgreSQL uses a search path to determine which schema to use when an unqualified object name (without a schema) is referenced. The public schema is the default schema and is included in the default search path.

Common Operations:

Creating a Schema:

CREATE SCHEMA schema_name;

Creating a Table within a Schema:

CREATE TABLE schema_name.table_name (
    column_name data_type [constraints]
);

Dropping a Schema:

To drop a schema without dropping its contained objects:

DROP SCHEMA schema_name;

To drop a schema along with all of its contained objects:

DROP SCHEMA schema_name CASCADE;

Setting the Search Path:

To set the schema search path for the current session:

SET search_path TO schema_name1, schema_name2;

To view the current search path:

SHOW search_path;

Usage Notes:

When you create an object without qualifying it with a schema name, PostgreSQL will save it in the first schema listed in your current search path.
If you do not provide a schema when referencing an object, PostgreSQL will look in each of the schemas in the search path and use the first one where it finds a match.
A common usage pattern is to have different schemas for different purposes or roles. For example, you might have a production schema and a testing schema within the same database.
The concept of a schema in PostgreSQL is similar to that in many other RDBMS systems, but it's important to note that in some databases, the term "schema" is synonymous with "database." In PostgreSQL, schemas are a level below databases in the hierarchy.

In conclusion, schemas in PostgreSQL provide a powerful way to organize, manage, and segregate database objects, making it easier to structure and secure your data in multi-user and multi-application environments.

How to create a schema in PostgreSQL: Schemas in PostgreSQL allow you to organize database objects. Use the CREATE SCHEMA statement.
```
CREATE SCHEMA your_schema;
```

Listing all schemas in PostgreSQL:

SELECT schema_name FROM information_schema.schemata;

Changing the current schema in PostgreSQL:
```
SET search_path TO your_schema;
```
Schema ownership in PostgreSQL: Schemas are owned by database users. The creator is usually the owner, but ownership can be transferred using ALTER SCHEMA.
```
ALTER SCHEMA your_schema OWNER TO new_owner;
```

Granting and revoking privileges on a schema in PostgreSQL:

GRANT USAGE ON SCHEMA your_schema TO your_user;
REVOKE ALL ON SCHEMA your_schema FROM your_user;

Schema search path in PostgreSQL: The search path determines the order in which schemas are searched when an object is referenced without a schema name.
```
SET search_path TO your_schema, public;
```

Creating tables in a specific schema in PostgreSQL:

CREATE TABLE your_schema.your_table (
   column1 datatype,
   column2 datatype
);

Altering a schema in PostgreSQL: Modify a schema's attributes using the ALTER SCHEMA statement.
```
ALTER SCHEMA your_schema RENAME TO new_schema;
```
Dropping a schema in PostgreSQL:
```
DROP SCHEMA your_schema;
```

Schema and stored procedures in PostgreSQL:

CREATE OR REPLACE FUNCTION your_schema.your_function()
RETURNS void AS $$
BEGIN
   -- Function logic
END;
$$ LANGUAGE plpgsql;

Schema and views in PostgreSQL:

CREATE VIEW your_schema.your_view AS
SELECT column1, column2 FROM your_table;

Schema and triggers in PostgreSQL:

CREATE TRIGGER your_schema.your_trigger
AFTER INSERT ON your_table
FOR EACH ROW
EXECUTE FUNCTION your_schema.your_function();