PostgreSql Part 1
- PostGresql Installation
- Version History
- Environment Structure
- Query Tool Intro
- Databse and table creatation
DataBase - Meet_May_21
create table EmployeeDetails(EmployeeId serial primary key,FirstName varchar(60),
LastName varchar(60),Place varchar(100),Country varchar(40),PhoneNo varchar(40),password varchar(100));
----------------------------------
create table RoomDetails(RoomId serial primary key ,RoomName varchar(100),RoomSize varchar(20))
create table MeetingDetails(MeetingId serial primary key,TimeFrom time ,TimeTo time,
RoomId Integer references RoomDetails(RoomId),
MeetingTitle text,MeetingDate date);
create table MeetingEmployees(MemberId serial primary key,
EmployeeId integer references employeedetails(employeeid) ,
MeetingId integer references meetingdetails(meetingid));
----------------------------------
Insert into RoomDetails(RoomName,RoomSize)
values('Abdul kalam','10'),
('aryabhata','15'),
('Nasa','20'),
('vivekananda','10'),
('Rohini','5');
insert into EmployeeDetails(FirstName,LastName,Place,Country,PhoneNo,password)
values('Vasanth', 'R','SGRI', 'India','8056512886','hello'),
('Ram', 'S', 'SGRI', 'India', '9366180976','hello'),
('Ravi', 'E', 'SGRI','India', '123456789','hello'),
('Krishna','R', 'SGRI', 'India', '987654321','hello'),
('Divya', 'R', 'SGRI', 'India', '7502036282','hello');
INSERT INTO MeetingDetails(TimeFrom,TimeTo,RoomId,MeetingTitle,MeetingDate)
Values('8:30 PM','8:45 AM','1','Project Analyse', '06-07-2019'),
('9:30 PM','9:45 AM','1','Project Analyse', '08-07-2019');
insert into MeetingEmployees(EmployeeId,MeetingId)
values(1,1);
------------------------------------------------------
SELECT now(),
now()::timestamp,
now() AT TIME ZONE 'CST',
now()::timestamp AT TIME ZONE 'CST'
SELECT CURRENT_DATE;
------------------------------------------------------
call SP_insertMeetingDetails('09:00 am','11:00 pm','1','General Meet', '06-07-2019',1)
CREATE OR REPLACE PROCEDURE public.sp_insertmeetingdetails(
utimefrom time,
utimeto time,
uroomid integer,
umeetingtitle character varying,
umeetingdate timestamp without time zone,
uemployeeid integer)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
insert into MeetingDetails(TimeFrom,TimeTo,RoomId, MeetingTitle,MeetingDate)
values(uTimeFrom,uTimeTo,uRoomId, uMeetingTitle,uMeetingDate);
insert into MeetingEmployees(EmployeeId,MeetingId)
values(uEmployeeId,(select MeetingId from MeetingDetails order by MeetingId desc
limit 1));
END;
$BODY$;
----------------------
call sp_logindetails('1','helsslo','s');
fetch all in "s";
CREATE OR REPLACE PROCEDURE public.sp_logindetails(
userid integer,
upassword character varying,
INOUT s refcursor)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
if exists(select from public.employeedetails where employeeid = userid and
password = upassword )then
open s for select 'login successful' as status;
else
open s for select 'Login Faild' as status;
end if;
END;
$BODY$;
-------------------
call sp_getroomnames('d');
fetch all in "d";
CREATE OR REPLACE PROCEDURE public.sp_getroomnames(
INOUT uroom refcursor)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
open uRoom for select * from roomdetails;
END;
$BODY$;
------------------------------
call sp_getemployeenames('ref','8056');
fetch all in "ref";
CREATE OR REPLACE PROCEDURE public.sp_getemployeenames(
INOUT unames refcursor,
unamekey character varying)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
open uNames for select employeeid,firstname,lastname,phoneno from EmployeeDetails
where FirstName ILIKE '%'||sp_getemployeenames.uNameKey||'%' or
LastName ILIKE '%'||sp_getemployeenames.uNameKey||'%' or
PhoneNo ILIKE '%'||sp_getemployeenames.uNameKey||'%';
END;
$BODY$;
----------------------------
create table EmployeeDetails2(EmployeeId serial primary key,FirstName varchar(60),
LastName varchar(60),Place varchar(100),Country varchar(40),
PhoneNo varchar(40),password varchar(100));
ALTER TABLE EmployeeDetails2
RENAME Place TO location;
ALTER TABLE EmployeeDetails2
ADD COLUMN City VARCHAR,
ADD COLUMN Phone_no VARCHAR;
ALTER TABLE EmployeeDetails2
DROP COLUMN IF EXISTS City;
ALTER TABLE EmployeeDetails2 ALTER COLUMN Phone_no SET NOT NULL;
ALTER TABLE EmployeeDetails2
ALTER COLUMN
Phone_no DROP NOT NULL;
The syntax for creating a schema
CREATE SCHEMA schema_name;
ALTER SCHEMA myschema
RENAME TO Schema1;
-------------------------------
select * from fn_getemployeenames1('1','v');
fetch all in "1";
CREATE OR REPLACE FUNCTION public.fn_getemployeenames1(ref1 refcursor,
unamekey character varying)
RETURNS SETOF refcursor
LANGUAGE 'plpgsql'
COST 100
VOLATILE
ROWS 1000
AS $BODY$
BEGIN
open ref1 for select employeeid,firstname,lastname,phoneno from EmployeeDetails
where FirstName ILIKE '%'||unamekey||'%' or
LastName ILIKE '%'||unamekey||'%' or
PhoneNo ILIKE '%'||unamekey||'%';
RETURN NEXT ref1;
END; $BODY$;
------------------------------------------------------
ALTER TABLE Student_information ALTER COLUMN Stu_name TYPE VARCHAR;
ALTER TABLE table_name
RENAME column_name TO new_column_name;
ALTER TABLE Persons
ADD COLUMN City VARCHAR,
ADD COLUMN Phone_no VARCHAR;
ALTER TABLE table_name
DROP COLUMN IF EXISTS column_name;
ALTER TABLE Product DROP COLUMN cate_id;
Add a column
After that we will add a new column named Latitude with the help of below command:
ALTER TABLE employeedetails1
ADD COLUMN test text;
Drop a column
To delete the Latitude column from the Station table, we will use the below command:
ALTER TABLE employeedetails1
DROP COLUMN test;
Rename a column
To rename the St_Name column to Name, we use the below command:
ALTER TABLE employeedetails1
RENAME COLUMN test TO Name;
Rename a table
To rename the Station table to Station1, we will use the below command:
ALTER TABLE employeedetails1
RENAME TO Station1;
Change column type
The below command is used to modify the type of St_City and St_State columns from Char to Varchar in Station1 table:
ALTER TABLE Station1
ALTER COLUMN test TYPE Varchar(30),
ALTER COLUMN St_State TYPE Varchar(20);
Add NOT NULL constraint
If we want to add the NOT NULL constraint to St_City column in Station1 table, we will use the below command:
ALTER TABLE Station1 ALTER COLUMN St_City SET NOT NULL;
Remove NOT NULL constraint
If we want to delete the NOT NULL constraint from the St_City column in the Station1 table, we will run the below command:
ALTER TABLE Station1
ALTER COLUMN
St_City DROP NOT NULL;
Add PRIMARY KEY constraint9
If we want to add a primary key constraint in the Station1 table, we will use the below command.
ALTER TABLE Station1 ADD PRIMARY KEY (St_id);
The syntax for creating a schema
CREATE SCHEMA schema_name;
ALTER SCHEMA myschema
RENAME TO Schema1;
------------------------------------------------------
1
DATEPART( datepart , date )
PostgreSQL:
1
2
date_part( text , timestamp )
date_part( text , interval )
SELECT date_part('century',TIMESTAMP '2017-01-01');
SELECT date_part('year',TIMESTAMP '2017-01-01');
SELECT date_part('month',TIMESTAMP '2017-09-30');
SELECT date_part('day',TIMESTAMP '2017-03-18 10:20:30');
SELECT date_part('hour',TIMESTAMP '2017-03-18 10:20:30') h,
date_part('minute',TIMESTAMP '2017-03-18 10:20:30') m,
date_part('second',TIMESTAMP '2017-03-18 10:20:30') s;
------------------------------------------------------
ISNULL
ISNULL function must be replaced by COALESCE function in PostgreSQL.
Example
MS SQL Server:
1
ISNULL(exp, replacement)
PostgreSQL:
------------------------------------------------------
First Not null
SELECT
COALESCE (1, 2);
SELECT
COALESCE (NULL, 2 , 1);
------------------------------------------------------
COALESCE(exp, replacement)
SPACE
SPACE function in MS SQL Server must be replaced by REPEAT function in PostgreSQL.
Example
MS SQL Server:
1
SPACE($n)
Where $n is the number of spaces to be returned.
PostgreSQL:
1
REPEAT(‘ ’, $n)
SELECT repeat('test___', 5),repeat('*--*', 5);
------------------------------------------------------
DATEADD
PostgreSQL does not provide DATEADD function similar to MS SQL Server, you can use datetime arithmetic with interval literals to get the same results.
Example
MS SQL Server:
1
2
--Add 2 day to the current date
SELECT DATEADD(day, 2, GETDATE());
PostgreSQL:
1
2
--Add 2 day to the current date
SELECT CURRENT_DATE + INTERVAL '2 day';
------------------------------------------------------
String Concatenation
MS SQL Server uses ‘+’ for String Concatenation whereas PostgreSQL uses ‘||’ for the same.
Example
MS SQL Server:
1
SELECT FirstName + LastName FROM employee;
PostgreSQL:
1
SELECT FirstName || LastName FROM employeedetails;
select (firstname ||' ' || lastname) as Names from employeedetails
------------------------------------------------------
CHARINDEX
There is CHARINDEX function in PostgreSQL. You can replace this function by PostgreSQL equivalent POSITION function.
Example
MS SQL Server:
1
SELECT CHARINDEX('our', 'resource');
PostgreSQL:
1
SELECT POSITION('our' in 'resource');
------------------------------------------------------
GETDATE
GETDATE function returns the current date and time. There is no GETDATE function in PostgreSQL, but there is NOW() function for the same purpose. If there are multiple occurrences of the GETDATE function then you can automate them using extension. Please check how to create modules using extension.
Example
MS SQL Server:
1
SELECT GETDATE();
PostgreSQL:
1
SELECT NOW();
CREATE TABLE employees(
id INT GENERATED ALWAYS AS IDENTITY,
first_name VARCHAR(40) NOT NULL,
last_name VARCHAR(40) NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE employee_audits (
id INT GENERATED ALWAYS AS IDENTITY,
employee_id INT NOT NULL,
last_name VARCHAR(40) NOT NULL,
changed_on TIMESTAMP(6) NOT NULL
);
CREATE OR REPLACE FUNCTION log_last_name_changes()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS
$$
BEGIN
IF NEW.last_name <> OLD.last_name THEN
INSERT INTO employee_audits(employee_id,last_name,changed_on)
VALUES(OLD.id,OLD.last_name,now());
END IF;
RETURN NEW;
END;
$$
CREATE TRIGGER last_name_changes
BEFORE UPDATE
ON employees
FOR EACH ROW
EXECUTE PROCEDURE log_last_name_changes();
INSERT INTO employees (first_name, last_name)
VALUES ('John', 'Doe');
INSERT INTO employees (first_name, last_name)
VALUES ('Lily', 'Bush');
SELECT * FROM employees;
UPDATE employees
SET last_name = 'test3'
WHERE ID = 2;
SELECT * FROM employees;
SELECT * FROM employee_audits;
DISABLE TRIGGER
Suppose you want to disable the trigger associated with the employeestable, you can use the following statement:
ALTER TABLE employees
DISABLE TRIGGER log_last_name_changes;
Code language: SQL (Structured Query Language) (sql)
To disable all triggers associated with the employees table, you use the following statement:
ALTER TABLE employees
DISABLE TRIGGER ALL;
Enabling trigger example
The following statement enables the salary_before_update trigger on the employees table:
ALTER TABLE employees
ENABLE TRIGGER salary_before_update;
The following example enables all triggers that belong to the employees table:
ALTER TABLE employees
ENABLE TRIGGER ALL;
Comments
Post a Comment