Creating a PostgreSQL Database, the Use of Database Templates, and Simple Command Line Tool Commands

A PostgreSQL server installation includes database templates. These are used when creating new databases.

By default, template1 is copied when you create a new database. This makes it easy to create copies of a customized database design. You can customize template1, and then easily make as many copies as you like, and they will all start out with the same objects, such as tables, languages, etc.

Specify template0 to create a standard, out-of-the-box database with no custom additions that may have been added to your server installation. Make sure that you do not make any changes to template0.

Create a database using template0, from a command line

A database can be created in various ways. Here we will create one from the command line, using the tool PostgreSQL createdb.

By default, the logged in user will be the owner of a newly created database.

While logged in as user postgres, from a command line, create a database named exampledb, using template0:

bash-4.2$ createdb -e --template=template0 exampledb
SELECT pg_catalog.set_config('search_path', '', false)
CREATE DATABASE exampledb TEMPLATE template0;

Start psql, the command line tool:

bash-4.2$ psql
psql (10.3)
Type "help" for help.

List the databases, and see that the new database, exampledb, has been added

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 exampledb | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | 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)

Connect to exampledb

postgres=# \c exampledb
You are now connected to database "exampledb" as user "postgres".

Create a table

exampledb=# create table t1 (
col1 varchar(50)
);
CREATE TABLE

View details about the new table

exampledb=# \dS t1
                        Table "public.t1"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 col1   | character varying(50) |           |          |

psql, the Postgresql command line tool, features a long list of commands that are cryptic, even by command line tool standards. It is easy to look up the commands, though.

Start psql

bash-4.2$ psql
psql (10.3)
Type "help" for help.

View help

Now type \? to view a long list of commands to interact with the database.

Type \h to view another long list of commands to create, alter, and drop database objects such as tables and views, etc.

Quit psql

Type \q