PG Casts

Listing and Creating Databases

Episode #23 hosted by Mary Lee

Hey everyone, today we're going to look at how to list and create databases in Postgres.

Postgres Version 11


To see a list of all of the databases we currently have, we can use the \list metacommand.

This command can also be shortened to just \l.

To create a new database we type create database followed by our desired database name.

create database example;

While creating a new database, we can also set our desired encoding, owner, collation, and a few other options. These settings get passed after the database name to the create database command.

An important thing to note when trying to use any encoding setting that is not the default on a new database is that we always have to pass the template option as well, setting it to template0. This is to prevent any potential data corruptions from cloning from the default template1 database.

You can see that if we don’t specify the template, Postgres throws an error when trying to create our new database.

create database example encoding 'sql_ascii' lc_collate 'C';

However, once we pass the template, we’re good to go.

create database example encoding 'sql_ascii' lc_collate 'C' template template0;

If we use our list metacommand again, we can see that our new database is now included in the list.

Thanks for watching!