Developer's Guide to PostgreSQL on Linux: Creating Databases

By Adam McQuistan in PostgreSQL  09/30/2019 Comment

Creating PostgreSQL Databases

Introduction

This is the third article of a series featuring a collection of useful tasks for administering PostgreSQL version 11 on the Linux operating systems Ubuntu 18 LTS as well as CentOS 7. For those unfamiliar, PostgreSQL is a powerful and robust Object Relational Database which is freely available as open source software and widely popular. In this article I show how to create databases using both SQL statements along with the createdb utility program.

Series Contents

Creating Databases with SQL

Databases can be created in PostgreSQL using traditional SQL as in the following set of commands which issues a CREATE DATABASE statement in the psql shell to define a database named test.

$ psql
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
Type "help" for help.

postgres=# CREATE DATABASE test;
CREATE DATABASE
postgres=# \q

Creating Databases Using createdb Utility Program

PostgreSQL also provides a utility program, createdb, which can be used to create databases. To accomplish this you issue the createdb program name followed by the name of the database you are creating. Additionally, I prefer to add the --echo flag to the command which prints out the underlying SQL statements that are executed against the PostgreSQL cluster.

$ createdb test2 --echo
SELECT pg_catalog.set_config('search_path', '', false)
CREATE DATABASE test2;

Notice that this program is used from the standard Linux shell no the psql interactive PostgreSQL shell.


Now when I list the databases in the psql shell I see the new test and test2 datatabases.

$ psql
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
Type "help" for help.

postgres=# \l
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges   
-----------+----------+----------+---------+---------+-----------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 test      | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 test2     | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
(5 rows)

Resources for Learning More About PostgreSQL

Conclusion

In this article I demonstrated two methods for creating database within a PostgreSQL cluster. Specifically, I gave examples of creating databases using traditional SQL statements plus using the createdb PostgreSQL utiltity program.

Share with friends and colleagues

[[ likes ]] likes

Community favorites for PostgreSQL

theCodingInterface