Let's look at the list of the databases in the cluster, using the system catalog:
α=> SELECT datname FROM pg_database;
datname ----------- postgres student template1 template0 (4 rows)
The same information can be viewed by the following psql command (we'll ignore additional columns for now):
α=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
student | student | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
An interesting feature of psql is the possibility to show SQL statements, which are sent to the server to fulfill the command:
α=> \set ECHO_HIDDEN on
α=> \l
********* QUERY **********
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
student | student | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
Disable the echo.
α=> \set ECHO_HIDDEN off
A new database is cloned from template1 database by default.
α=> CREATE DATABASE test;
CREATE DATABASE
α=> \c test
You are now connected to database "test" as user "student".
α=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
student | student | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | student | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)
List of the schemas from the system catalog:
α=> SELECT nspname FROM pg_namespace;
nspname
--------------------
pg_toast
pg_temp_1
pg_toast_temp_1
pg_catalog
public
information_schema
(6 rows)
Some of the listed schemas are already discussed (public, pg_catalog, information_schema); others will be discussed later on.
There is also a special psql command (dn = describe namespace):
α=> \dn
List of schemas Name | Owner --------+---------- public | postgres (1 row)
This command does not show the system schemas by default, but we can add S modifier for that (the modifier also works for many other commands):
α=> \dnS
List of schemas
Name | Owner
--------------------+----------
information_schema | postgres
pg_catalog | postgres
pg_temp_1 | postgres
pg_toast | postgres
pg_toast_temp_1 | postgres
public | postgres
(6 rows)
Another useful modifier is the plus sign, which instructs psql to show more details:
α=> \dn+
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
(1 row)
Let's create a new schema, say, for some special objects:
α=> CREATE SCHEMA special;
CREATE SCHEMA
α=> \dn
List of schemas Name | Owner ---------+---------- public | postgres special | student (2 rows)
What schema will a new table belong (given that the name is not qualified)?
To answer this question, we should look at the search path.
α=> SHOW search_path;
search_path ----------------- "$user", public (1 row)
The "$user" clause denotes a schema named the same as the current user (student in our case). There is no such schema, so effectively it is ignored.
In order to simplify analysis we can use the function showing the actual search path including implicitly added schemas:
α=> SELECT current_schemas(true);
current_schemas
---------------------
{pg_catalog,public}
(1 row)
Now let's create a table:
α=> CREATE TABLE t(n integer);
CREATE TABLE
List of tables is shown by \dt command:
α=> \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+---------
public | t | table | student
(1 row)
And here is how we can get the list of objects in the public schema from the system catalog:
α=> SELECT relname, relnamespace FROM pg_class WHERE relnamespace = 'public'::regnamespace;
relname | relnamespace ---------+-------------- t | 2200 (1 row)
Relnamespace column has OID datatype; the following is the corresponding row in pg_namespace table:
α=> SELECT oid, nspname FROM pg_namespace WHERE nspname = 'public';
oid | nspname ------+--------- 2200 | public (1 row)
Type cast of a schema name to regnamespace datatype allows to simplify the query and get rid of explicit join of tables. Additional reg-types are defined for some other system catalog tables.
An object can be moved between schemas. Schemas define a logical layout, so all we need to move an object to another schema is to change a piece of information in the system catalog. The data remains physically in place.
α=> ALTER TABLE t SET SCHEMA special;
ALTER TABLE
To check, we'll use the ability to specify a template in \dt command:
α=> \dt public.*
Did not find any relation named "public.*".
α=> \dt special.*
List of relations
Schema | Name | Type | Owner
---------+------+-------+---------
special | t | table | student
(1 row)
Now table t can be accessed by the qualified name:
α=> SELECT * FROM special.t;
n --- (0 rows)
And it won't be found without the explicitly specified schema name:
α=> SELECT * FROM t;
ERROR: relation "t" does not exist
LINE 1: SELECT * FROM t;
^
We can set the search path in the following way:
α=> SET search_path = public, special;
SET
And now the same table can be accessed without specifying the schema name.
α=> SELECT * FROM t;
n --- (0 rows)
We set the configuration parameter on session level, which means that the value will reset upon reconnection. Setting the value on cluster level is probably not a good idea either, as this path may not be applicable in all cases.
Fortunately we can set the value for a specific database:
α=> ALTER DATABASE test SET search_path = public, special;
ALTER DATABASE
The path will be used for all new connections to the database. Let's check:
α=> \c test
You are now connected to database "test" as user "student".
α=> SHOW search_path;
search_path ----------------- public, special (1 row)
A schema cannot be dropped unless it does not contain any objects:
α=> DROP SCHEMA special;
ERROR: cannot drop schema special because other objects depend on it DETAIL: table t depends on schema special HINT: Use DROP ... CASCADE to drop the dependent objects too.
It is possible to drop a schema along with all its objects:
α=> DROP SCHEMA special CASCADE;
NOTICE: drop cascades to table t DROP SCHEMA
To drop a database there must be no active connections to it:
α=> \conninfo
You are connected to database "test" as user "student" via socket in "/var/run/postgresql" at port "5432".
α=> \c postgres
You are now connected to database "postgres" as user "student".
α=> DROP DATABASE test;
DROP DATABASE
The end of demonstration.