How To Create Sequence In PostgreSQL And Usage Of Sequence || Table Primary Key || Stored Procedure

#knowledge360 #akramsohail #akramsohailproject
You Are Hearty Welcomed To My Channel Knowledge 360. Here I post technical videos, mainly related to computer science and programming. I am posting project videos with coding explanations. I make tutorial videos on Technical Topics. Stay Connected, Stay Tuned, Study Smart.
– Knowledge 360 (Akram Sohail)

Follow me on Social Media
————————————————–
Facebook – https://www.facebook.com/sonu.babu.5872682/
Instagram – https://www.instagram.com/akkubakku007/
WhatsApp – https://wa.me/+919090484904
LinkedIn – https://www.linkedin.com/in/akram-sohail-499489131/
Skype – https://join.skype.com/invite/snliyulHrEBb
Google+ – https://plus.google.com/u/0/116728183002228924227
Blog – https://knowledge360blog.blogspot.in/

Business/Mentorship/Projects – [email protected]
Source Codes – https://knowledge360blog.blogspot.com/2022/08/how-to-create-sequence-in-postgresql.html

Topics
———
Oracle
MySQL
PostgreSQL
SQL Server
PHP
C
C++
Java
JavaScript
HTML/CSS
jQuery
Ajax
Bootstrap
Angular
Linux
Ubuntu
Windows

Description
——————

How To Create Sequence In PostgreSQL And Usage Of Sequence || Table Primary Key || Stored Procedure

CREATE SEQUENCE tbl_emp_seq
START WITH 1 — VALUE WILL START WITH 1
INCREMENT BY 1 — IT WILL GET INCREMENT BY 1
MINVALUE 1 — MINIMUM VALUE CAN BE 1
MAXVALUE 10 — MAXIMUM VALUE CAN BE 10
CYCLE; — CYCLE MEANS, AFTER REACHING MAXVALUE, WHICH IS 10 HERE, THE SEQUENCE WILL START AGAIN FROM 1

— NOW LET’S SEE THE SEQUENCE VALUE
— USE THE BELOW STATEMENT FOR SEQUENCE VALUE

SELECT NEXTVAL(‘tbl_emp_seq’);

— THE VALUE REACHED MAXVALUE, NOW IT WILL AGAIN START FROM 1

— NOW LET’S SEE THE CASE OF NO CYCLE
— IN CASE OF NO CYCLE, THE SEQUENCE WON’T START AGAIN FROM 1
— IT WILL THROW AN ERROR AFTER REACHING THE MAX VALUE

DROP SEQUENCE tbl_emp_seq;

CREATE SEQUENCE tbl_emp_seq
START WITH 1 — VALUE WILL START WITH 1
INCREMENT BY 1 — IT WILL GET INCREMENT BY 1
MINVALUE 1 — MINIMUM VALUE CAN BE 1
MAXVALUE 10 — MAXIMUM VALUE CAN BE 10
NO CYCLE;

SELECT NEXTVAL(‘tbl_emp_seq’);

— HERE THE MAX VALUE IS REACHED, SO IF WE TRY TO GET THE VALUE AFTER THAT, WE WILL GET AN ERROR

ERROR: nextval: reached maximum value of sequence “tbl_emp_seq” (10)
SQL state: 2200H

— THIS IS WHY WE USUALLY KEEP THE MAXVALUE VERY HIGH AS MUCH POSSIBLE

— NOW LET’S CREATE AN ACTUAL SEQUENCE AND USE IN A TABLE TO GENERATE VALUES AUTOMATICALLY

CREATE SEQUENCE tbl_emp_seq
START WITH 1 — VALUE WILL START WITH 1
INCREMENT BY 1 — IT WILL GET INCREMENT BY 1
MINVALUE 1 — MINIMUM VALUE CAN BE 1
MAXVALUE 10000000000 — MAXIMUM VALUE CAN BE 10
NO CYCLE;

CREATE TABLE EMP
(
EMP_ID INTEGER DEFAULT NEXTVAL(‘tbl_emp_seq’),
EMP_NAME VARCHAR(50),
SALARY NUMERIC(5,2)
);

INSERT INTO EMP(EMP_NAME,SALARY) VALUES (‘AKRAM’,100.56);
INSERT INTO EMP(EMP_NAME,SALARY) VALUES (‘SOHAIL’,670.56);
INSERT INTO EMP(EMP_NAME,SALARY) VALUES (‘KNOWLEDGE 360’,757.87);

SELECT * FROM EMP;

— WE CAN SEE, THE EMP_ID VALUES ARE GENERATED FROM SEQUENCE

— NOW LET’S USE THE SEQUENCE IN A STORED PROCEDURE

CREATE OR REPLACE PROCEDURE public.EMP_PROC()
LANGUAGE ‘plpgsql’
AS $BODY$
DECLARE
V_EMP_SEQ_VAL INTEGER;
BEGIN
SELECT NEXTVAL(‘tbl_emp_seq’) into V_EMP_SEQ_VAL;
insert into emp(emp_id,emp_name,salary) values (V_EMP_SEQ_VAL,’New Emp’,455.24);
END;
$BODY$;

call public.EMP_PROC();

select * from emp;

SELECT NEXTVAL(‘tbl_emp_seq’);

SELECT CURRVAL(‘tbl_emp_seq’);

— also we can see the current value of a sequence

— If you have any doubt, please ask me in the comments.
— Subscribe the channel to get the videos updates.