There are multiple methods and parameters when creating a database. I will talk about them in this article.
There are 4 methods to create the database:
1. Creating a database via pg_admin
2. Creating a database with pl\pgsql via pg_admin
3. Creating a database with the help of psql client
4. As I mentioned in the Command Line Database Operations tutorial, we can create a database with the createdb command.
Let’s start by creating a database through the database Pg_admin, which is the first method.
I explained the connection via pgadmin and the necessary settings to connect in my other article, so I start with creating a database directly without going through it again.
Right click on the databases tab under the server we created and select the database tab in the create tab.
Let’s examine the general tab in the opened window.
In this window, we specify the database name in the tab that says database.
In the Owner section, we select the database owner, that is, the user who is authorized to do everything in the database.
When we do nothing, the postgres user comes up.
In the comment section, we enter the description. (This is optional, you can not enter a description if you want.)
When we come to the definition part, the encoding part catches our eye first.
What is encoding?
They are character sets, that is, they are character sets that are used to translate every letter and character we press from the keyboard into computer language.
We make choices because of the characters they contain, so why utf-8 ?
UTF-8 is an encoding method. UTF stands for Unicode Transformation Format.
When data is to be written to a file or passed over the network, it must be converted to bytes. This conversion process is called encoding.
The most commonly used method for encoding Unicode characters is UTF-8.
It tries to encode the UTF-8 Unicode character set in the most efficient way, that is, taking up the fewest bytes.
That’s why UTF8 is usually chosen.
In the template section, we specify which database to take as an example. I have mentioned in previous threads.
Tablespace: It is the part where the data written to the database is kept. The pg_default tablespace is automatically created when the cluster is installed.
Collation: Collation is the part where we make the settings about how and according to which language the rows in the table are sorted.
Character Type: Selected as C. In the queries selected in this option, it has an important place in the features such as like order by, that is, the query order on text data.
Security: On the Security tab, there are security options. The Security tab is used to assign privileges and define security tags.
Parameters: In this section, if we want to define a parameter while creating the database, we can use it here.
SQL: The SQL part gives the SQL equivalent of the operations performed.
We select the relevant parameters.
Let’s move on to step 2 to create the database.
Creating a database with SQL commands via pg_admin
Click on the Query Tool tab above the postgres database via pgAdmin.
After doing this, the query tab opens and we write the SQL commands in this section.
Here we create a database using the above parameters.
Example database SQL is as follows.
CREATE DATABASE f1
OWNER = postgres —Database owner
ENCODING = ‘UTF8’
LC_COLLATE = ‘C’
LC_CTYPE = ‘C’
TABLESPACE = pg_default
CONNECTION LIMIT = -1; —Database connection limit.
Creating a database with the help of psql client
We connect with psql by opening our virtual machine or you can connect using one of the ssh programs.
We switch to the postgres user with the su postgres command.
We connect to postgres by typing psql.
Here we create the database by typing the SQL.
And our database was created.
Creating a database with the createdb command
We go to the /usr/pgsql-10/bin/ directory and with the help of the programs here, we create a database without using the psql client.
First, we switch to the postgres user using the su postgres command.
We go to the directory using the command cd /usr/pgsql-10/bin/ . After seeing it with the ls command, we create a database with the help of the following command.