Postgresql: Create schema in specific database

Kickstarting a new database

If you're starting from scratch, create the database first. Then, switch to it and create your schema:

CREATE DATABASE foo;  -- Bam! New database
\c foo  -- Slide into your new DB
CREATE SCHEMA yourschema;  -- Schema time! Let's roll


Check for schema's uniqueness before creation to avoid duplicate embarrassment, like wearing the same outfit to a party.

CREATE SCHEMA IF NOT EXISTS yourschema;  -- Always check before you wreck


And if you pack all this inside a script with \connect, you've got a neat package, as compact as a camper van:

CREATE DATABASE foo;
\connect foo
CREATE SCHEMA yourschema;


Remember, these steps are your faithful pals, whether you're in the manual world or the scripted universe.

Replay with scripts

Why repeat the same steps manually if you can have a .sql script taking care of the boring stuff? Use \connect in scripts to specify the database:

CREATE DATABASE projectdb;  -- Fresh DB, out of the oven
\connect projectdb  -- Time to move in!
CREATE SCHEMA project_schema AUTHORIZATION db_user;  -- Secure the premises. Trespassers will be prosecuted


Run the script from the command line, like an orchestrated symphony:

psql -f setup_script.sql  -- Let the magic happen!


Schema management

As your database grows, your schema may require tweaks or additional siblings. Here's how you can keep your database structure in check:

1. Tally current schemas with a query to information_schema.schemata:

   SELECT schema_name FROM information_schema.schemata;  -- Roll call!
   

2. Christen an existing schema with a new name using ALTER SCHEMA:

   ALTER SCHEMA old_schema RENAME TO new_schema;  -- Old name. We don't do that here
   

3. When cleanup is in repertoire, cautiously drop a schema ensuring it's not needed:

   DROP SCHEMA if_exists_schema CASCADE;  -- Goodbye, old friend
   

   The CASCADE option, though powerful, will bulldoze all contained objects. Tread lightly!

Role-oriented schema creation

Involving users in schema management ensures role-based access control. Create and manage schemas with precise ownership and privileges:

• To conceive a schema and grant privileges:

  CREATE SCHEMA user_schema;
  GRANT ALL ON SCHEMA user_schema TO some_user;  -- Passing the baton
  

• Or, form a schema directly assigned to a user:

  CREATE SCHEMA user_schema AUTHORIZATION some_user;  -- Signed, sealed, delivered!
  

By defining boundaries, you shape a well-managed and secure database environment.