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)
);
