PostgreSQL -Create Schema

You can create a new schema using the CREATE SCHEMA statement. A schema is a namespace that can contain tables, views, functions, and other database objects. Here’s the basic syntax for creating a schema:

CREATE SCHEMA schema_name;

Replace schema_name with the name you want to give to your new schema. Here’s a complete example:

CREATE SCHEMA my_schema;

This statement will create a new schema named my_schema in the current database.

You can also specify the owner of the schema using the AUTHORIZATION clause:

CREATE SCHEMA my_schema AUTHORIZATION some_user;

some_user is the name of the PostgreSQL user who should own the schema. The user specified will have certain privileges and control over the objects within that schema.

After creating a schema, you can use it to organize and manage your database objects. For example, you can create tables within the schema like this:

CREATE TABLE my_schema.my_table ( id serial PRIMARY KEY, name text );

This creates a table named my_table within the my_schema schema. To refer to objects within a specific schema, you should prefix them with the schema name, such as my_schema.my_table.

Keep in mind that you need appropriate privileges (e.g., CREATE privilege on the database) to create schemas, and the user specified as the schema owner should also have the necessary privileges for creating objects within that schema.