Working with Schemas

CREATE SCHEMA app;
CREATE SCHEMA IF NOT EXISTS reporting AUTHORIZATION analyst;

\dn
\dn+                  -- with ACLs
SELECT schema_name FROM information_schema.schemata;
SELECT nspname FROM pg_namespace WHERE nspname NOT LIKE 'pg_%';

ALTER SCHEMA app OWNER TO app_owner;

SET search_path = app, public;            -- session
ALTER ROLE alice SET search_path = app, public;
ALTER DATABASE shop SET search_path = app, public;

SHOW search_path;
SELECT current_schemas(true);    -- includes implicit (pg_catalog)
SELECT current_schema();

CREATE SCHEMA AUTHORIZATION alice;        -- schema named 'alice'
CREATE SCHEMA reports AUTHORIZATION analyst;

SELECT * FROM app.customers;
SELECT app.full_name(c) FROM app.customers c;

ALTER SCHEMA reports RENAME TO reporting;

ALTER SCHEMA app OWNER TO new_owner;
REASSIGN OWNED BY old_owner TO new_owner;  -- all objects

DROP SCHEMA staging;                -- fails if not empty
DROP SCHEMA IF EXISTS staging;

DROP SCHEMA staging CASCADE;