EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • 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
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Auto Increment
 

PostgreSQL Auto Increment

Payal Udhani
Article byPayal Udhani
EDUCBA
Reviewed byRavi Rathore

Updated May 9, 2023

PostgreSQL Auto Increment

 

 

Introduction to PostgreSQL Auto Increment

The following article provides an outline of PostgreSQL Auto Increment. PostgreSQL has a special database object called a SEQUENCE object that lists ordered integer values. Developers often use the SEQUENCE when describing a unique key or primary key or a column that auto-increments in database tables. When developers declare a particular column as a pseudo-type SERIAL, PostgreSQL generates a SEQUENCE object.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

SEQUENCE Database Object

  • First, you need to understand how the SEQUENCE object works.
  • SEQUENCE is completely a schema-bound object defined by the user according to their special requirements. {11, 12 ,13, 14, 15,…} is completely a different sequence than {100, 99, 98,97,..} sequence.
  • Developers can create a sequence using the ‘CREATE SEQUENCE’ statement.
  • We can define the minimum and maximum value, incremental step value, name, and owner of the SEQUENCE and cache space, which needs to be pre-allocated space in the memory of the sequenced list.

SERIAL pseudo-type

  • While creating a table in PostgreSQL, if we declare any column of the type SERIAL, internally, the SERIAL pseudo-type also creates a new SEQUENCE object for that column and table default values.
  • That further helps us achieve the auto-incrementation of the values of certain columns declared as SERIAL type.

Internal Working

When we declare the column named “student_id” of type SERIAL in the table creation query for the “educba” table in the following way.

Code:

CREATE TABLE educba(student_id SERIAL,name VARCHAR(100));

Internally, PostgreSQL executes a series of commands.

Some of them are as follows:

CREATE SEQUENCE educba_student_id_seq;
CREATE TABLE educba (student_id integer NOT NULL DEFAULT nextval('educba_student_id_seq'), name VARCHAR(100));
ALTER SEQUENCE educba_student_id_seq OWNED BY educba.student_id;

PostgreSQL executes the above-mentioned internal queries when declaring any column of type SERIAL in a table.

  • When a table with a SERIAL column called “student_id” is created in PostgreSQL, the system automatically generates a new SEQUENCE object for that column. It assigns the default value of the “student_id” column to the next value of that sequence.
  • As a sequence always generates a non-null integer value, the column student_id will be assigned with the NOT NULL constraint.
  • Deleting the student_id column should also lead to deleting the SEQUENCE object associated with it.
  • For this purpose, the owner of the created sequence educba_student_id_seq is set to the student_id column of the educba table.

Types of SERIAL pseudo-types

In PostgreSQL, we can create a SERIAL pseudo-type that can belong to either of the following three types.

  • SMALLSERIAL
  • SERIAL
  • BIGSERIAL

Which differ in their storage space and range limits. This is similar to short int, int, and long int.

The storage space and range of all the three pseudo-types of the serial are as follows:

Pseudo-type name Storage size required Range of its values in the list
SMALLSERIAL 2 bytes of space 1 – 32,767
SERIAL 4 bytes of space 1 – 2,147,483,647
BIGSERIAL 8 bytes of space 1 – 9,223,372,036,854,775,807

Example of SERIAL pseudo-type

Let us now take an example of how serial pseudo-type helps us achieve auto-incrementation. Declaring a column of type SERIAL does not automatically create an index on that column or consider it as its primary key. To do so, you will externally need to define the column as the PRIMARY KEY.

Code:

CREATE TABLE educba(student_id SERIAL PRIMARY KEY ,name VARCHAR(100) NOT NULL);

Output:

postgreSQL Auto Increment 1

To verify our table creation, let us fire the \dt command.

Output:

postgreSQL Auto Increment 2

As it can be seen that the table named educba is created successfully. When you insert the records in the table with auto-incremented valued columns, you can either skip inserting those column values or specify the DEFAULT keyword for those columns in your INSERT query statement. We will insert the records using both methods. Firstly, skipping the student_id column.

Code:

INSERT INTO educba(name) VALUES('Payal');

Output:

insert into educba

Saying that 1 row is inserted successfully. Now, let us insert a record specifying the DEFAULT value for our student_id column.

Code:

INSERT INTO educba(student_id,name) VALUES(DEFAULT,'Piyush');

Output:

postgreSQL Auto Increment 4

Saying our one more row is inserted. Now, let us check our educba table records and see whether values are inserted for the student_id column and auto-incremented.

Code:

SELECT * FROM educba;

Output:

check records

Hence, we can see that the student_id column works as the auto-incremented field now.

Methods of Sequence Value

Developers use methods related to the sequence object created in the database, such as fetching the name of the sequence object and retrieving the current maximum value of that sequence object. Developers use the ‘pg_get_serial_sequence’ method to get the name of a sequence object created for a particular table’s serial-typed column. It takes two parameters the name of the table and the name of a column of that table.

Code:

pg_get_serial_sequence('tableName','columnName');

Let us retrieve the name of the sequence object created for the student_id column.

Code:

SELECT pg_get_serial_sequence('educba','student_id');

Output:

postgreSQL Auto Increment 6JPG

So, educba_student_id_seq is the sequence object’s name.

We will retrieve the maximum value or the last value assigned by this sequence object to the column student_id using the method currval(nameOfSequence) that takes only one parameter: the name of the sequence object.

Code:

SELECT currval(pg_get_serial_sequence('educba', 'student_id'));

Output:

select currval

It’s essential to know that the sequence value of a serial-typed column is not a transaction-safe value. In two instances, accessing the same database table inserts the record. Both will get different sequence values and if one of them reverts or roll back the transaction, then the value that that session retrieved remains unchanged, creating a gap in the table values of auto-incremented fields.

Conclusion- PostgreSQL Auto Increment

PostgreSQL achieves auto-incrementation by declaring columns of the pseudo-datatype ‘SERIAL’ internally managed by the SEQUENCE database object. The SEQUENCE database object is an ordered list.

Recommended Articles

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

  1. PostgreSQL Boolean
  2. PostgreSQL RANK()
  3. CAST in PostgreSQL
  4. PostgreSQL Notify

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - 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
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
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 Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW