How to install and use PostgreSQL on Ubuntu 20.04?

Overview

Many websites and apps use relational database management systems (RDBMS) as a fundamental building block. They offer a systematic technique to gather, arrange, and access data.

A RDBMS called PostgreSQL, sometimes known as Postgres, offers an implementation of the SQL. It complies with standards and includes several cutting-edge capabilities, like concurrency without read locks and transactions that can be trusted.

Learn step-by-step how to install and use PostgreSQL on Ubuntu 20.04 with this easy-to-follow guide. Start using this powerful open-source database today!

Prerequisites

There are certain prerequisites that need to be met before you begin:

• Ubuntu 20.04 server

• SSH supported code editor

• Basic knowledge of database

Get Started

Postgres packages by default present in ubuntu's repositories, so we will use apt packaging system to install it. Follow the below steps:

Step 1: Update server's local package index

sudo apt update

Step 2: Install Postgres packages with -contrib package.

sudo apt install postgresql postgresql-contrib

Step 3: Check if the server is working properly using systemct1 start command.

sudo systemctl start postgresql.service

Now, the PostgreSQL is installed, let's see how it works.

Moving to Postgres Account

Step 1: On your server, change to the Postgres account.

sudo -i -u postgres

Step 2: Access the PostgreSQL prompt using the following command.

psql

Step 3: Exit the PostgreSQL prompt using:

postgres=# \q

New role creation

Create a new role by typing:

createuser --interactive

New Database creation

Type the following command to create a new database:

createdb user2

Connecting to a database

Use the following command to connect to database:

psql -d 'database name'

Creating and deleting Table

Syntex for creating a table:

CREATE TABLE 'Name of the table' (
    column_1 type (length) col_constraints,
    column_2 type (length),
    column_3 type (length)
);

For example:
CREATE TABLE check_price ( 
  id serial PRIMARY KEY,
  name VARCHAR (30),
  description VARCHAR (30),
  price numeric CHECK (price > 0)
  );

To view the table type:

\d

Adding data in a table

INSERT INTO check_price (id, name, description) VALUES ('1', 'Jack', 'Test');

To view this data, type

SELECT * FROM check_price;

Delete data from table

DELETE FROM check_price WHERE id = '1';

View table again

SELECT * FROM check_price;

Conclusion

PostgreSQL is now configured on your Ubuntu 20.04 server.