| created on | January 8, 2022 |
advantages of using a schema
Schemas can be thought of like a directory on an OS level, with the exception, that schemas
can’t be nested. Schemas provide for the following advantages:
- organize database objects like i.e. tables and sequences into logical groups,
which makes them more manageable.
- allow users and apps to use one database without interfering with each other.
- assist in understanding komplex database structures.
listing all schemas in a database
sampledb=> \dn
sample output:
List of schemas
Name | Owner
----------+----------
address | rudolf
i18n | rudolf
iso | rudolf
public | postgres
security | rudolf
(5 rows)
listing tables in a schema
listing all tables in schema iso:
sampledb=> \dt iso.*
sample output:
List of relations
Schema | Name | Type | Owner
---------+-------+-------+--------
security | user | table | rudolf
security | group | table | rudolf
(2 rows)
listing all permissions in a schema
show all permissions of schema ‘iso’:
postgresql=> select pc.relname, pc.relacl from pg_class pc, pg_namespace pn where
pc.relnamespace=pn.oid and pn.nspname='iso';
or short, to list all permissions of table country in schema iso
sampledb=> \z iso.country