A guide to creating databases using Postgres - (r)

-sidebar-toc>

This hands-on guide explores the process of creating databases, tables as well as delete databases from Postgres. In addition, it provides instructions on how to accomplish the same task using a database management software such as the Adminer.

Getting Started with Postgres

Before you begin, ensure that you've got Postgres running on your system. If you don't have it, download the necessary file and follow the instructions for installation.

Keep in mind that these commands are demonstrated on macOS however they'll function effortlessly on any OS.

Once Postgres is installed, run the following command on your terminal to ensure that everything is working smoothly

postgres -V

This command should return the version number of your Postgres installation:

How To Connect to PostgreSQL Database Server

Now you've set up Postgres on your computer and are now ready to build databases. What is the best way to access your database? That's where the Postgres Interactive Terminal, commonly called psql can help. Psql is a terminal-based interface to Postgres which allows users to send queries to Postgres and view the query results.

During installation, Postgres creates a default superuser in the OS (OS) that has ultimate access within the database. Log in to the psql console as the default superuser with this command:

psql postgres

When you run this command, you'll see your terminal switch to postsgres=# which means you're logged in as the default superuser.

One of the most significant benefits that you can get from psql is its meta-commands. These powerful tools let administrators manage databases, such as connecting to databases or displaying tables, all without understanding the exact SQL commands.

To use a meta-command in Psql, you must start by typing a backslash ( \) then the command. Here are a few instances:

• \c -- connects you to a particular database.
• "l" -- The list of databases available on the server.
• dt displays every table in the database.

How To Make Postgres Databases

In the case of databases, it's an excellent procedure to observe the principle of least privilege by creating an account for a user that has specific rights. But, in the interest of simplicity, in this tutorial we'll create and maintain databases with the default superuser.

In the beginning, you must execute the following meta-command in order to list all users of your Postgres server:

\du

If you've never added new users, you should only see the default superuser:

The default superuser could appear as either Postgres or as an OS username, based the configuration of your system.

By default, the superuser has no password. However, for database administration further on, create an account password by using the following command:

\password 

Enter your password when prompted and verify it. Now, you're ready to create databases in the Postgres server. The syntax used to create the database is CREATE DBA DATABASE (database name).

Start with creating a database called sales:

CREATE DATABASE sales;

Following appears following the creation of a database successfully:

Then, you can create two more databases for employees and employees using these commands:

CREATE DATABASE customers;
 CREATE DATABASE employees;

After that, you've created three databases within your local Postgres server. To show all your databases Use this meta-command

\l

The three databases that you've made to date! You can ignore the other database in the image as those databases come with the Postgres installation default.

Now, you can connect to any database. The meta-command for connecting with any database can be \c .

Use the following command to connect to the sale database:

\c sales

This message will pop up in your terminal:

Once you have connected to the database, you are able to change to a different database on the server with the identical command. For instance, starting from to the sale database, you can run the following command in order to join the customers database:

Customers

Create Tables

To start, you'll need make tables that will populate the database with information. The syntax used to create a table in Postgres follows the following structure:

CREATE TABLE  (
   ,
   ,
   ,
 ...
 ...
   
 );

Start by connecting with the sale database.

\c sales

Create the table products comprising three columns that cannot be null Product_id, product_name and the quantity sold:

CREATE TABLE products(
Product ID INT NOT NULL,
 Product_name text not null,Quantity_sold INT NOT NULL);

It is expected to see the following output if the operation is success:

Next, use the meta-command in this article to confirm that you've created the table of products table:

\dt

This command displays all tables in your database -- in this case just one table. If you're connected to the selling database, you should get the following results:

Create two tables in the employees database. The first table will list salaries, and the second lists addresses. To make these tables, execute the commands below:

*c employeesCREATE TABLES salary(
Employee_id INT NOT NULL,Name of employee TEXT not null,
 Employee_salary INT NOT NULL
NOT NULL);
 
 CREATE TABLE address(
Employee_id INT NOT NULL,
employee_country Text not NULL,Employee_zipcode NOT NULL);

Finally, confirm that you have created these tables by running the \dt meta-command. This is the output you will see:

How To Delete Postgres Databases

Deleting a database is as straightforward as making one. The way to delete an existing database is "DROP DATABASE" database-name>.

You don't need to connect to a particular database to delete it. So, if you want to delete the customers database, you can execute this command on any database you are connected to:

Drop DATABASE customers

You should see this screen on successful deletion:

You can confirm you are sure that your customer database no longer exists by listing the databases on your local Postgres server by using the \l meta-command.

Handling Postgres Database Operations With Administrator

At this point, you've acquired the basic knowledge of Postgres through the creation of databases, making tables, as well as deleting databases using Command Line.

You'll also need to download an Adminer PHP script to manage your database using Adminer. Open your terminal once to begin the built-in web server that runs PHP files, and navigate to the location where you've placed the adminer PHP file:

Cd path/to/Adminerer.php file

Then, you should start the server with this command:

PHP"-S" 127.0.0.1:8000

All you need to do is use the Adminer UI within your web browser. Type the following address in your web browser: http://localhost:8000/

The Adminer User Interface (UI) in your web browser:

To connect to into your regional Postgres server Follow the steps below while filling in the fields on this page:

1. Choose PostgreSQL in your System area.
2. Server must be filled in to localhost.
3. For Username, type the name of the superuser, such as "postgres," or the username of your computer's operating system.
4. For Password For Password, enter the password set for the superuser within the "Create Databases" section.
5. Don't leave your field Database field unfilled.

On successful authentication You'll get a listing of the databases that you've previously created like the one below. If you're working with Windows there is a chance that you will encounter an error that says, "None of the supported PHP extensions (PgSQL and PDO_PgSQL,) is available." If you encounter this, modify your php.ini file and allow the extensions.

To start a new database, simply click to the Create Database link:

Give your database a name and clients and click to save them. Save button.

After that, confirm that you've created the customer database by clicking on the Server hyperlink. It is as follows:

You'll see the customers database now. Click the customers link to join it.

As you can see, there are no tables in the database. Click to click the button to create a table link to make an entirely new table. The name is Locations.

Input the correct columns that match the picture below. Click to save. save button

It should be possible to access the table within the database of your clients' database:

Click the Server link again to view all of your databases. Make sure to tick the checkbox for your customers. Ticking this checkbox will allow you to delete the database of your customers. checkbox for customers database will enable the drop button below. Click drop to erase the database. You'll receive a confirmation about the deletion of your database:

Summary

You've now learned how to create databases, create tables in your database and then delete your database on the local Postgres server via by using the command line. Additionally, you've learned that it's easy to accomplish these tasks by using a database management tool such as Administrator.

Even though these and many other command line techniques of table and database administration are effective but Adminer's point and click interface makes performing these tasks even simpler.

Jeremy Holcombe

Content & Marketing Editor , WordPress Web Developer, and Content writer. In addition to everything related to WordPress I like the beach, golf, as well as movies. Additionally, I'm tall and have issues ;).

•                 Website                                          
•                 LinkedIn