PostgreSQL Overview

Setting up the server

To get start with postgreSQL, download the Postgres.app & pgadmin 4(for macOS).
Postgres.app is a simple, native macOS app that runs in the menubar without the need of an installer.
pgAdmin 4 is a feature rich open source PostgreSQL client.

Open Postgres.app > Click on Start - This will start the postgres server

Open pgadmin 4 - This will start the server & opens http://127.0.0.1:56791/browser/ in the browser.
Right Click on server > Create > Server > Fill the details as below & Save,

You will be connected to the server which was started by Postgres.app. You will see the below hierarchy on the right side.

Right click on Databases > Create > Database > enter any name for database (ex : employee_db)
Select Tools > Query Tool - This will open a Query Editor.

Hands on

Run the below queries one by one and watch what it does,

create table employee(employee_id integer not null,
   employee_name text not null,
   education_qualification text not null,
   gender varchar(6) not null,
   employee_role text not null,
   payload text);
      
--- INSERTING VALUES ---
insert into employee
values('1001', 'Arun Velusamy', 'Engineering', 'male', 'application developer');

insert into employee
values('1002','Ashok Subburaj', 'Engineering', 'male', 'application developer');

insert into employee
values('1003','Lakshmi Velan', 'PHD', 'female', 'Lead Engineer');

insert into employee
values('1004','Veerammal Ramasamy', 'MBA', 'female', 'Product designer');

insert into employee
values('1005','Gautham Kumarasamy', 'Not Available', 'male', 'Cheif Executive');

--- SELECT THE EMPLOYEE TABLE ---
select * from employee;

select to_jsonb(emp)
from
 (
  select employee_id,employee_name,education_qualification,gender,employee_role
  from employee
 ) emp;

--- UPDATE THE PAYLOAD ---
update employee
set payload = 
 (
  select to_jsonb(emp)
  from (
   select employee_id,employee_name,education_qualification,gender,employee_role
   from employee
  ) emp
where employee.employee_id = emp.employee_id);

--- SELECT THE EMPLOYEE TABLE ---
select * from employee;

--- ALTER TABLE TO DROP 2 COLUMNS ---
alter table employee
drop column education_qualification;

alter table employee
drop column gender;

select * from employee;

--- SELECT VALUES FROM JSON PAYLOAD COLUMN ---
select employee.employee_id, employee.employee_name, employee.employee_role,
 emp_payload.education_qualification, emp_payload.gender
from employee, jsonb_to_record(employee.payload::jsonb) as emp_payload (
 education_qualification text,
 gender varchar(6)
);