PostgreSQL | Schema | List Created Schemas
This page explains how to list schemas that have already been created in PostgreSQL.
Use the \dn Command
First, look at how to use a psql meta-command. To list schemas that have already been created, connect to the target database and run the following.
devkuma=# \dn
스키마(schema) 목록
이름 | 소유주
---------------+----------
myschema | postgres
orange_schema | orange
public | postgres
testschema | postgres
(4개 행)
devkuma=#
In the currently connected devkuma database, in addition to the default public schema, the myschema schema owned by the postgres role and the orange_schema schema owned by the orange role have been created.
If you add + to the \dn command, you can view access privileges in addition to the basic schema information.
devkuma=# \dn+
스키마(schema) 목록
이름 | 소유주 | 액세스 권한 | 설명
---------------+----------+----------------------+------------------------
myschema | postgres | |
orange_schema | orange | |
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
testschema | postgres | |
(4개 행)
devkuma=#
If you connect to another database and run the \dn command, schemas created in the newly connected database are listed. Connect to the postgres database and run the \dn command as shown below.
devkuma=# \c postgres
접속정보: 데이터베이스="postgres", 사용자="postgres".
postgres=# \dn
스키마(schema) 목록
이름 | 소유주
--------+----------
public | postgres
(1개 행)
postgres=#
The currently connected postgres database has only the default public schema.
Query the pg_namespace System Catalog
Next, look at how to get schema information from pg_namespace, one of PostgreSQL’s system catalogs. System catalogs are used by PostgreSQL’s management system to manage information such as databases and tables. pg_namespace has the following columns.
| Name | Data Type | Description |
|---|---|---|
| oid | oid | Identifier. It cannot be obtained unless explicitly specified. |
| nspname | name | Namespace name |
| nspowner | oid | Namespace owner |
| nspacl | aclitem[] | Access privilege list |
Connect to the target database and query the following columns from pg_namespace.
devkuma=# select nspname, nspowner, nspacl from pg_namespace;
nspname | nspowner | nspacl
--------------------+----------+-------------------------------------
pg_toast | 10 |
pg_temp_1 | 10 |
pg_toast_temp_1 | 10 |
pg_catalog | 10 | {postgres=UC/postgres,=U/postgres}
public | 10 | {postgres=UC/postgres,=UC/postgres}
information_schema | 10 | {postgres=UC/postgres,=U/postgres}
myschema | 10 |
orange_schema | 24635 |
testschema | 10 |
(9개 행)
devkuma=#
The list of schemas included in the currently connected database has been retrieved. Schemas that start with pg_ are used by the PostgreSQL system. information_schema is also used by the system.
Also, nspowner, which represents the owner of the schema, displays the oid value from the pg_authid system catalog that manages role data. To get the actual role name, refer to rolname in pg_authid. For example, run the following.
devkuma=# select nspname, pg_authid.rolname as schemaowner, nspacl
devkuma-# from pg_namespace
devkuma-# join pg_authid on pg_authid.oid = pg_namespace.nspowner;
nspname | schemaowner | nspacl
--------------------+-------------+-------------------------------------
pg_toast | postgres |
pg_temp_1 | postgres |
pg_toast_temp_1 | postgres |
pg_catalog | postgres | {postgres=UC/postgres,=U/postgres}
public | postgres | {postgres=UC/postgres,=UC/postgres}
information_schema | postgres | {postgres=UC/postgres,=U/postgres}
myschema | postgres |
orange_schema | orange |
testschema | postgres |
(9개 행)
devkuma=#
The schema list has been retrieved together with each schema owner’s role name.
–
This page explained how to retrieve the list of created schemas.