EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL SERIAL

PostgreSQL SERIAL

Priya Pedamkar
Article byPriya Pedamkar

Updated May 8, 2023

PostgreSQL SERIAL

Introduction to PostgreSQL SERIAL

PostgreSQL serial data type is used to define the auto-increment number of column in a table; PostgreSQL serial will generate a serial sequence of integer numbers. We can also restart serial no after the creation of the table using alter command, the storage size of the serial data type is 4 bytes, and the range of serial data type in PostgreSQL is 1 to 2, 147, 483, 647. Using a serial data type, we can store up to 2, 147, 483, and 647 numbers in our table. If we use bigserial, then the range of this serial data type is 1 to 9, 223, 372, 036, 854, 775, and 807, and the storage size is 8 bytes.

ADVERTISEMENT
Popular Course in this category
POSTGRESQL Course Bundle - 5 Courses in 1 | 1 Mock Test

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax:

Below is the syntax:

CREATE TABLE table_name (Table name that we have used to create new table) (column_name1(Column name on which we have creating a Serial data type)SERIAL, column_name2 data_type,column_nameN data_type ()Data type that we defined to column);

Serial is equivalent to the following statement(Sequence) in PostgreSQL.

CREATE SEQUENCE(Create new sequence) table_name_id_seq (Sequence name );
CREATE TABLE table_name (Table name that we have used to create new table)
(Column_nameinteger (Data type) NOT NULL (Defined not null constraint to the column) DEFAULTNextval ('table_name_id_seq'));
ALTER SEQUENCE (Alter sequence using alter command) table_name_id_seq
OWNED BY table_name.column_name;

Parameter description of the above syntax is as follows:

  • Create: Create a table by using serial data type in PostgreSQL. We can define serial data type in a table column.
  • Column 1 to column N: Column name used while creating a column in PostgreSQL. In this column, we have defined the serial data type.
  • Data type: Data type defines the type of data we have stored in the table. A data type is most important while creating a table.
  • Table name: Table name on which column we have defining serial data type.
  • Serial: Data type is used to define auto increment number of column in a table; PostgreSQL serial will generate a serial sequence of integer numbers.
  • Sequence: Equivalent to a serial data type, we have created a sequence in PostgreSQL.
  • Sequence name: We have created a new sequence equivalent to a serial data type in PostgreSQL.
  • Alter sequence: We have altered the sequence and changed the sequence number in PostgreSQL.

How PostgreSQL SERIAL Function Works?

  • After defining a serial data type to the column, postgresql will first create a sequence and set the next value generated by this sequence.
  • The second time it will add a not-null constraint on the serial column because it always generates an integer value.
  • The third time it assigned the owner to the column. We can change the owner of the sequence using alter command.
  • PostgreSQL will provide three serial types: SMALLSERIAL, SERIAL, and BIGSERIAL.
  • The serial data type’s storage size is 4 bytes, and the range of serial data type in PostgreSQL is 1 to 2, 147, 483, 647. We can store up to 2, 147, 483, and 647 numbers in our table using a serial data type.
  • If we use bigserial, then the range of this serial data type is 1 to 9, 223, 372, 036, 854, 775, and 807, and the storage size is 8 bytes.
  • If we use data type as a small serial, then the range of serial data type is 1 to 32, 767.
  • PostgreSQL serial data type is used to define the auto increment number of columns in a table; it will generate a serial sequence of integer numbers.
  • Using serial data type will not automatically create the index on the column; we need to create it explicitly.
  • We can check the existing sequence by using the pg_get_serial_sequence function.
  • The figure below shows the emp_serial1_id_seq function by using the pg_get_serial_sequence function.
  • We need to pass the table name in the first argument and the column name in the second argument in the same function.

Code:

select pg_get_serial_sequence('Emp_Serial1','emp_id');

Output:

PostGRESQL SERIAL1

Examples of PostgreSQL SERIAL

Given below are the examples:

Example #1

Define serial data type at the time of table creation.

  • The example below defines a serial data type to the column at the table creation time.
  • We have defined the serial data type on the emp_id column at the time of table creation. Creating this data type on the column “emp_serial_emp_id_seq” is automatically created.

Code:

CREATE TABLE Emp_Serial (emp_id SERIAL NOT NULL, emp_name character(10) NOT NULL, emp_address character(20) NOT NULL, emp_phone character(14), emp_salary INT NOT NULL, date_of_joining date NOT NULL);

Output:

PostGRESQL SERIAL2

Example #2

Alter the table to add serial data type after table creation.

  • The below example shows define a serial data type to the column after table creation.
  • We have defined the serial data type on the emp_id column after the creation of the table.

Code:

ALTER TABLE Emp_Serial drop column emp_id;

Output:

alter table

Code:

ALTER TABLE Emp_Serial ADD column emp_id SERIAL;

Output:

alter table

Code:

\d+ Emp_Serial;

Output:

postgreSQL SERIAL5JPG

Example #3

Create serial data type using creating a sequence.

  • We have created a serial data type using a sequence. We have created the sequence name as emp_serial1_id_seq.
  • After sequence creation, we created the table name emp_serial1 and defined the next value as a sequence name.
  • After creating a table, we have altered a sequence name and defined a table name and column name for this sequence.

Code:

CREATE SEQUENCE EMP_SERIAL1_id_seq;

Output:

using creating a sequence

Code:

CREATE TABLE Emp_Serial1 (emp_id integer NOT NULL DEFAULT Nextval ('Emp_Serial1_id_seq'));

Output:

postgreSQL SERIAL7JPG

Code:

ALTER SEQUENCE EMP_SERIAL1_id_seq OWNED BY Emp_Serial1.emp_id;

Output:

using creating a sequence

Example #4

Alter table to restart value of serial data type.

  • The below example shows that we have to alter the Emp_Serial table after inserting the first value. After inserting the first value, we have altered the serial value of the table.

Code:

INSERT INTO Emp_Serial (emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES ('ABC', 'Pune', '1234567890', 20000, '01-01-2020');

Output:

postgreSQL SERIAL9JPG

Code:

ALTER SEQUENCE emp_serial_emp_id_seq RESTART WITH 51;

Output:

Alter table to restart value

Code:

INSERT INTO Emp_Serial (emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES ('ABC', 'Pune', '1234567890', 20000, '01-01-2020');

Output:

Alter table to restart value

Code:

select * from Emp_Serial;

Output:

postgreSQL SERIAL12JPG

Conclusion

PostgreSQL serial data type is used to define the auto increment number of column in a table; PostgreSQL serial will generate a serial sequence of integer numbers. We can also restart the serial number after creating a table using alter command in PostgreSQL; the serial data type’s storage size is 4 bytes.

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL SERIAL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL ROLLUP
  2. PostgreSQL IN Operator
  3. FETCH PostgreSQL
  4. Guide to PostgreSQL RANDOM
ADVERTISEMENT
PROGRAMMING LANGUAGES Course Bundle - 54 Courses in 1 | 4 Mock Tests
338+ Hours of HD Videos
54 Courses
4 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
SELENIUM Course Bundle - 15 Courses in 1 | 9 Mock Tests
39+ Hours of HD Videos
15 Courses
9 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
IOT System Course Bundle - 7 Courses in 1
43+ Hours of HD Videos
7 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
JENKINS Course Bundle - 6 Courses in 1
15+ Hour of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW