As a database administrator, you must know how to create a database. n this comprehensive guide, I will demonstrate the step-by-step process of setting up a database in PostgreSQL. By following these instructions, you will gain a solid understanding of how to create a database in PostgreSQL effectively.

CREATE DATABASE – The Command

To create a database in PostgreSQL we use CREATE DATABASE SQL command. Here is the syntax of the CREATE DATABASE DDL.

CREATE DATABASE database_name
OWNER = owner_name
TEMPLATE = template
ENCODING = encoding			
LC_COLLATE = collate			
LC_CTYPE = ctype
TABLESPACE = tablespace_name;

The process of creating a database starts with the reserved phrase “CREATE DATABASE” followed by specifying the desired NAME for the database. You have the freedom to choose a name that suits your needs. Additionally, the keyword “OWNER” allows you to designate the username of the individual who will have complete administrative control over the database. By default, the user executing the “CREATE DATABASE” command becomes the owner of the tablespace.

Next, the keyword “TEMPLATE” comes into play, enabling you to specify a template database that will serve as the foundation for creating the new database. The default template used by PostgreSQL is “template1,” which facilitates the swift creation of a new database with a predefined structure.

Furthermore, the “ENCODING” keyword allows you to specify the character set for your database, determining how characters are stored and interpreted.

The “LC_COLLATE” parameter, which stands for LOCALE COLLATION, dictates the sorting order of text within the database. For instance, if set to English, words such as “cherry,” “banana,” and “apple” will be sorted alphabetically as “Apple,” “banana,” and “cherry.” However, if a different language is chosen, the sorting will differ accordingly. By omitting this parameter, the LC_COLLATE value from the template database will be inherited.

Similarly, the “LC_TYPE” parameter, or locale character classification, governs the categorization of characters (e.g., letters, numbers, symbols) within the database. For instance, in English, the letters ‘a,’ ‘e,’ ‘i,’ ‘o,’ and ‘u’ would be classified as vowels. However, with a Spanish setting, the classification would differ accordingly.

Additionally, the “TABLESPACE” clause allows you to specify the tablespace where the database’s files will be stored on the disk. A tablespace serves as a designated location for storing a database’s data.

By understanding and implementing these steps, you can confidently create a new database in PostgreSQL while ensuring the desired configurations and settings are in place.

Connect With The PostgreSQL Container

Since I have installed my PostgreSQL database on Docker thus I will first connect with it. For that, I will use the DOCKER EXEC command like this –

Docker exec -it postgres_img psql -U postgres

In my previous tutorial, I demonstrated the process of connecting to PostgreSQL, and you can find the link to that video on your screen and in the description.

Create The Database

CREATE DATABASE library
OWNER = postgres
TEMPLATE = template0
ENCODING = UTF8
LC_COLLATE = 'en_US.utf8'
LC_CTYPE = 'en_US.utf8'
TABLESPACE = ts_users;

I am currently creating the database using the syntax mentioned earlier. The name assigned to this database is ‘Library’, and it is owned by the user ‘Postgres’, which is the default user created during the installation process. The creation of new users will be covered in future tutorials.

For this database, the template used is ‘template0’, serving as the foundation for its structure. The chosen encoding is UTF8, a widely adopted Unicode standard utilized for text representation in computers.

Regarding locale collation and character classification, I have set them to ‘en_US.utf8’, representing the English language using UTF-8 encoding. This ensures consistent sorting and categorization of text within the database.

Lastly, the tablespace utilized for this database is ‘ts_users’, the same tablespace created in the previous tutorial. By specifying this tablespace, the database’s data will be stored in the designated location on the disk.

By following these steps, the ‘Library’ database will be successfully created with the desired configuration and settings.

Check if the database is created or not.

In PostgreSQL, you can see all the databases that have been created by using a simple meta-command and that is –

> \l

In PostgreSQL, commands that start with a backslash are called Meta-commands

and “backslash l” is one of those meta commands which will show all the databases that are created.

Connect with the database

To connect with the database “library” we will use “\c” (the backslash C) meta-command like this

>\c library

If you want to check the current database with which you are currently connected then you can do so with the help of a very simple “SELECT” statement like this

>SELECT current_database();

Mastering database creation in PostgreSQL is crucial for database administrators. Follow the step-by-step process in this guide to set up well-structured databases. Connect to the PostgreSQL container, use the CREATE DATABASE command with customized parameters, and verify with \\l meta-command. Optimize performance and leverage PostgreSQL for data-driven insights. Embrace opportunities and become a proficient database administrator. Happy creating!