Database Administration - PostgreSQL 9 Administration - page 163

There are no additional options on the CREATE SCHEMA command.
To remove a schema named str , we can issue the following command:
DROP SCHEMA str;
Note that there isn't a CREATE OR REPLACE SCHEMA command, so when you want to create a
schema whether or not it already exists you can do the following:
DROP SCHEMA IF EXISTS newschema;
CREATE SCHEMA newschema;
The DROP SCHEMA won't work unless the schema is empty and unless you use the nuclear
option:
DROP SCHEMA IF EXISTS newschema CASCADE;
The nuclear option kills all known germs, and all of your database objects also.
There's more...
In the SQL Standard, you can also create a schema and the objects it contains all in one SQL
statement. PostgreSQL accepts this syntax if you need it, as follows:
CREATE SCHEMA foo
CREATE TABLE account
(id INTEGER NOT NULL PRIMARY KEY
,balance NUMERIC(50,2))
CREATE VIEW accountsample AS
SELECT *
FROM account
WHERE random() < 0.1;
Though mostly, I find it limiting. This syntax exists to allow creating more than one object
at the same time. That can be achieved more easily by using PostgreSQL's ability to allow
transactional DDL, which is discussed in an earlier recipe.
Schema-level privileges
Privileges can be for objects in a schema using the GRANT command, as follows:
GRANT SELECT ON ALL TABLES IN SCHEMA sharedschema TO PUBLIC;
Though this will only affect tables that already exist, tables created in the future will inherit
privileges defined by the ALTER DEFAULT PRIVILEGES command, for example:
ALTER DEFAULT PRIVILEGES IN SCHEMA sharedschema
GRANT SELECT ON TABLES TO PUBLIC;