Quick start with Postgres ⚡

0. Introduction

RDMS are with us for the long time & provides as an organized way to store and access information. In relational databases realm, PostgreSQL and MySQL has many similarities.
I am not going to bore you with the specific differences between these two, but if you are curious to read about this, I am mentioning 2 articles below here, that clarifies both pro and con about postgres.
1. Dzone's article
2. Uber's article

1. Installation

I will discuss moslty about installation in linux, but I will also mention links for windows and mac installation.

Installation steps: 1. update the current system:
sudo apt update
2. Install the postgresql & contrib package for additions tools.
sudo apt install postgresql postgres-contrib

great !! installtation is done, now you have postgres in your system.

2. Create

PostgresSQL comes with by default user and db named as postgres. so let's create a user of your choice first.
sudo -u postgres createuser --login --pwprompt test_user
It will show a prompt for password like below:
Enter password for new role: Enter it again:

Lets create a database now:
sudo -u postgres createdb --owner=test_user test_db

now this new "test_db" belongs to the "test_user" and it will be only accessed by test_user.
Now let's restart the postgres service:
sudo service postgresql restart

Now if you're not currently using user profile in which you want to use postgres, then either switch to the that user profile example: "postgres" or "test_user" or use psql as that user without switching.

option 1: switching to different user profile: sudo -i -u postgres
then
psql

option 2: or just use that user to open psql prompt:
sudo -u postgres psql

Great going !! now lets create our first DB.
sudo create -u postgres createdb test_db
or if you want to create db for another user, you can also do it.
sudo -u postgres createdb --owner=test_user test_db2
So far we have created a "user" & a "database" in postgreSQL.

Now understand how to create a table.
CREATE TABLE table_name ( column_name1 type (length of field) constraints, column_name2 type (length of field), column_name3 type (length of field) );

how to read above code ? here :
CREATE TABLE table_name (); is the standard way to initialize a new table. inside that we have columns.

1. column_name : name of the column (ex: name, date, username, password, etc.)
2. type: type of column (ex: Interger, varchar,boolean, etc.) with field length (ex: varchar(250) ).
3. constraints: conditions for the column (ex: NOT NULL, check (col_name in ("4 wheeler", "3 wheeler", "16 wheeler")), UNIQUE, etc.)

Let's create a table now:
CREATE TABLE flights ( flight_id serial PRIMARY KEY, type varchar (50) NOT NULL, size varchar (25) NOT NULL, location varchar(25) check (location in ('delhi', 'jaipur', 'kolkata', 'gujarat', 'chennai', 'bengalore', 'chandhigarh', 'meghalaya')), flight_date date, flight_time timestamp );
Above command will create a table which you can check by using "\d".

2. Insert

Now we have created user, database & table in postgreSQL, let's insert a entry.
INSERT INTO flights(type, size, location, flight_date, flight_time) VALUES('one-way', 'charter', 'delhi', '01-02-2022', '2015-08-07 05:00:01');
to check if the entered data is saved or not write :
SELECT * FROM flights;
And it will show the "flights" table with data in your terminal.


Awesome 🎊!! we have just learnt basics of postgres and also created & Inserted data in our postgres database. to learn further about more postgres topics in deep go to https://www.postgresqltutorial.com/ .

Thanks for reading this, Let me know if find any improvisation in this article, I am Prakash Pawar and you can follow me on twitter & Instagram . Thank you.