Working with Schemas
1. Creating Schema
CREATE SCHEMA app;
CREATE SCHEMA IF NOT EXISTS reporting AUTHORIZATION analyst;| Clause | Purpose |
|---|---|
| IF NOT EXISTS | Idempotent creation |
| AUTHORIZATION role | Sets owner |
| Embedded objects | CREATE SCHEMA app CREATE TABLE t(...) CREATE VIEW v AS ... |
2. Listing Schemas
\dn
\dn+ -- with ACLs
SELECT schema_name FROM information_schema.schemata;
SELECT nspname FROM pg_namespace WHERE nspname NOT LIKE 'pg_%';| Built-in | Role |
|---|---|
| public | Default schema |
| pg_catalog | System catalogs |
| information_schema | SQL standard metadata |
| pg_temp_* | Per-session temp objects |
3. Setting Schema Owner
ALTER SCHEMA app OWNER TO app_owner;| Effect | Detail |
|---|---|
| Privilege | Owner can drop/alter/create within schema |
| Existing objects | Not reowned automatically (use REASSIGN OWNED) |
4. Setting Search Path
SET search_path = app, public; -- session
ALTER ROLE alice SET search_path = app, public;
ALTER DATABASE shop SET search_path = app, public;| Token | Meaning |
|---|---|
| "$user" | Schema matching current role |
| public | Public schema |
| First match | Used for unqualified references |
5. Viewing Current Search Path
SHOW search_path;
SELECT current_schemas(true); -- includes implicit (pg_catalog)
SELECT current_schema();| Function | Returns |
|---|---|
| current_schema() | First non-temp schema in path |
| current_schemas(boolean) | Array of effective schemas |
6. Creating Schema with Authorization
CREATE SCHEMA AUTHORIZATION alice; -- schema named 'alice'
CREATE SCHEMA reports AUTHORIZATION analyst;| Pattern | Effect |
|---|---|
| AUTHORIZATION only | Schema name = role name |
| Name + AUTHORIZATION | Custom name, given owner |
7. Using Qualified Names
SELECT * FROM app.customers;
SELECT app.full_name(c) FROM app.customers c;| Form | Meaning |
|---|---|
| schema.table | Disambiguates across schemas |
| db.schema.table | Only allowed for current DB |
| "Schema".table | Quote case-sensitive identifiers |
8. Renaming Schema
ALTER SCHEMA reports RENAME TO reporting;| Requirement | Detail |
|---|---|
| Caller | Schema owner or superuser |
| Side effect | References by qualified name break |
9. Changing Schema Owner
ALTER SCHEMA app OWNER TO new_owner;
REASSIGN OWNED BY old_owner TO new_owner; -- all objects| Command | Scope |
|---|---|
| ALTER SCHEMA | Schema only |
| REASSIGN OWNED | Schema + all owned tables/funcs |
| DROP OWNED BY | Drop all objects of role |
10. Dropping Schema
DROP SCHEMA staging; -- fails if not empty
DROP SCHEMA IF EXISTS staging;| Mode | Behavior |
|---|---|
| default (RESTRICT) | Errors if objects exist |
| CASCADE | Drops dependent objects |
11. Dropping Schema Cascade
DROP SCHEMA staging CASCADE;Warning: CASCADE will silently drop foreign keys, views, sequences, and any object referencing the schema. Inspect dependencies first with
\d+ and pg_depend.| Pre-drop Check | Query |
|---|---|
| Tables | SELECT tablename FROM pg_tables WHERE schemaname='staging'; |
| Dependents | SELECT * FROM pg_depend WHERE refobjid='staging'::regnamespace; |