PostgreSQL Databases

System databases

• Two Template Databases:
• Template0
• Template1
• postgres:
• The default database created using the template1 database.
• If you do not specify a database at connection time, you will be connected to the postgres database.
• DVD Rental sample database.
• Downolad: https://www.postgresqltutorial.com/postgresql-sample-database/

Template Databases

Database creation actually works by copying an existing DB.

The template DBs are used for user database creation.

• Two ‘default’ Template Databases: Template0, Template1.
• Template1 is the default template.
• The 2 templates contains the same data.
• Template0 is more empty/"virgin" DB.
• New encoding and locale settings can be specified when copying template0, whereas a copy of template1 must use the same settings it does.
• More templates can be created.

Tablespace

Tablespaces define locations in the file system where the files representing database objects can be stored.

Tablespace creation must be done as a database superuser.

PostgreSQL comes with two default tablespaces:

• pg_default: stores all user data (the default tablespace).
• pg_global: stores all global data.

One tablespace can be used by multiple databases.

The physical location of tablespaces: <PostgreSQLFolder>\12\data .

Tables, indexes, and entire databases can be assigned to particular tablespaces: 

    CREATE TABLE foo(i int) TABLESPACE space1;

Database Creation

Database is created by cloning one of the templates.

Database is created in one of the tablespaces.

CREATE DATABASE name

 [ [ WITH ] [ OWNER [=] user_name ]

 [ TEMPLATE [=] template ]

 [ ENCODING [=] encoding ]

 [ LC_COLLATE [=] lc_collate ]

 [ LC_CTYPE [=] lc_ctype ]

 [ TABLESPACE [=] tablespace ]

 [ CONNECTION LIMIT [=] connlimit ] ]