EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

PostgreSQL Auto Increment

Home » Data Science » Data Science Tutorials » PostgreSQL Tutorial » PostgreSQL Auto Increment

PostgreSQL Auto Increment

Introduction to PostgreSQL Auto Increment

The following article provides an outline on PostgreSQL Auto Increment. In PostgreSQL, we have a special database object called a SEQUENCE object that lists ordered integer values. SEQUENCE is often used when we want to describe a unique key or primary key or column which is auto-incremented in database tables. SERIAL is a pseudo-type that generates a SEQUENCE object when a particular column is declared pseudo-type.

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 his/her special requirements. {11, 12 ,13, 14, 15,…} is completely a different sequence than {100, 99, 98,97,..} sequence.
  • A sequence can be created with the help of 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 in achieving the auto-incrementation of the values of certain columns declares as of type SERIAL.

Internal Working

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

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Code:

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

Then internally, there are a bunch of commands being executed.

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;

The above internal queries are executed while declaring any column of type SERIAL in the table.

  • A new SEQUENCE object is created for the column student_id, and the next value of that sequence is assigned to the default value of the student_id column.
  • As a sequence always generates a non-null integer value, the column student_id will be assigned with the NOT NULL constraint.
  • If the student_id column is deleted, then the SEQUENCE object associated with it should also be deleted.
  • 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 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. However, it is necessary to know that whenever a column is declared of the type SERIAL, it does not mean that an index is created on that column or that column is considered 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 that values are inserted for the student_id column and whether they are 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

Methods related to the sequence object created in the database like fetching the name of the sequence object and retrieving the current maximum value of that sequence object. pg_get_serial_sequence method is used 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, which is the name of the sequence object.

Code:

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

Output:

select currval

However, you need to know that the serial typed column’s sequence value is not the transaction-safe value. In two instances accessing the same table of the database insert the record then both will get different sequence value, and if one of them reverts or rollback the transaction, then the value which that session retrieved remains unchanged and hence creates a gap in the table values of auto-incremented fields.

Conclusion

In PostgreSQL, auto incrementation is achieved through declaring columns of pseudo-datatype SERIAL and is internally managed by the SEQUENCE database object, which is kind of an ordered list.

Recommended Articles

This is a guide to PostgreSQL Auto Increment. Here we discuss the introduction to PostgreSQL auto Increment with a SEQUENCE database object, types of SERIAL pseudo-types, and sequence value methods. You may also have a look at the following articles to learn more –

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

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

50+ projects

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

2 Shares
Share
Tweet
Share
Primary Sidebar
PostgreSQL Tutorial
  • Advanced
    • PostgreSQL Schema
    • Postgres List Schemas
    • PostgreSQL VARCHAR
    • Array in PostgreSQL
    • PostgreSQL DDL
    • PostgreSQL List Users
    • Postgres Default User
    • Postgres add user
    • PostgreSQL log_statement
    • PostgreSQL String Functions
    • PostgreSQL Compare Strings
    • PostgreSQL Text Search
    • PostgreSQL TEXT
    • PostgreSQL String Array
    • PostgreSQL Constraints
    • PostgreSQL UNIQUE Constraint
    • PostgreSQL INTERSECT
    • PostgreSQL Like
    • Cursors in PostgreSQL
    • PostgreSQL UNION ALL
    • Indexes in PostgreSQL
    • PostgreSQL Index Types
    • PostgreSQL REINDEX
    • PostgreSQL UNIQUE Index
    • PostgreSQL Clustered Index
    • PostgreSQL DROP INDEX
    • PostgreSQL DISTINCT
    • PostgreSQL FETCH
    • PostgreSQL RAISE EXCEPTION
    • PostgreSQL Auto Increment
    • Sequence in PostgreSQL
    • Wildcards in PostgreSQL
    • PostgreSQL Subquery
    • PostgreSQL Alias
    • PostgreSQL LIMIT
    • PostgreSQL Limit Offset
    • PostgreSQL LAG()
    • PostgreSQL Table
    • Postgres Show Tables
    • PostgreSQL Describe Table
    • PostgreSQL Lock Table
    • PostgreSQL ALTER TABLE
    • Postgres Rename Table
    • Postgres DROP Table
    • PostgreSQL Functions
    • PostgreSQL Math Functions
    • PostgreSQL Window Functions
    • Aggregate Functions in PostgreSQL
    • PostgreSQL Primary Key
    • Foreign Key in PostgreSQL
    • PostgreSQL Procedures
    • PostgreSQL Stored Procedures
    • PostgreSQL Views
    • PostgreSQL Materialized Views
    • Postgres Create View
    • PostgreSQL Triggers
    • PostgreSQL DROP TRIGGER
    • PostgreSQL Date Functions
    • PostgreSQL TO_DATE()
    • PostgreSQL Timestamp
    • PostgreSQL CURRENT_TIMESTAMP()
    • PostgreSQL Notify
    • PostgreSQL RANK()
    • PostgreSQL Select
    • PostgreSQL Average
    • PostgreSQL DATE_PART()
    • PostgreSQL EXECUTE
    • PostgreSQL COALESCE
    • PostgreSQL EXTRACT()
    • PostgreSQL Sort
    • PostgreSQL TO_CHAR
    • PostgreSQL Interval
    • PostgreSQL Number Types
    • PostgreSQL ROW_NUMBER
    • Alter Column in PostgreSQL
    • PostgreSQL Identity Column
    • PostgreSQL SPLIT_PART()
    • PostgreSQL CONCAT()
    • PostgreSQL replace
    • PostgreSQL TRIM()
    • PostgreSQL MAX
    • PostgreSQL DELETE
    • PostgreSQL Float
    • PostgreSQL OID
    • PostgreSQL log
    • PostgreSQL REGEXP_MATCHES()
    • PostgreSQL MD5 
    • PostgreSQL NOW()
    • PostgreSQL RANDOM
    • PostgreSQL round
    • PostgreSQL Trunc()
    • PostgreSQL TIME
    • PostgreSQL IS NULL
    • PostgreSQL CURRENT_TIME
    • PostgreSQL MOD()
    • Postgresql Count
    • PostgreSQL Datetime
    • PostgreSQL MIN()
    • PostgreSQL age()
    • PostgreSQL enum
    • PostgreSQL OR
    • PostgreSQL Wal
    • PostgreSQL NOT IN
    • PostgreSQL SET
    • PostgreSQL Current Date
    • PostgreSQL Compare Date
    • PostgreSQL SERIAL
    • PostgreSQL Database
    • PostgreSQL Clone Database
    • PostgreSQL Copy Database
    • PostgreSQL Restore Database
    • PostgreSQL DROP DATABASE
    • PostgreSQL ALTER DATABASE
    • Postgres DROP Database
    • Postgres Dump Database
    • PostgreSQL OFFSET
    • PostgreSQL GRANT
    • PostgreSQL COMMIT
    • PostgreSQL ROLLUP
    • PostgreSQL JSON
    • EXPLAIN ANALYZE in PostgreSQL
    • PostgreSQL Temporary Table
    • PostgreSQL cluster
    • PostgreSQL Replication
    • PostgreSQL Logical Replication
    • PostgreSQL flush privileges
    • PostgreSQL Tablespaces
    • CAST in PostgreSQL
    • PostgreSQL CTE
    • hstore in PostgreSQL
    • PostgreSQL DECODE()
    • PostgreSQL Vacuum
    • PostgreSQL EXCLUDE
    • Postgres Change Password
    • Postgres Delete Cascade
    • PostgreSQL EXCEPT
    • PostgreSQL Roles
    • PostgreSQL Link
    • PostgreSQL Partition
    • PostgreSQL column does not exist
    • PostgreSQL Log Queries
    • PostgreSQL escape single quote
    • PostgreSQL Query Optimization
    • PostgreSQL Character Varying
    • PostgreSQL Transaction
    • PostgreSQL Extensions
    • PostgreSQL Import CSV
    • PostgreSQL Client
    • PostgreSQL caching
    • PostgreSQL JDBC Driver
    • PostgreSQL Interview Questions
  • Basic
    • What is PostgreSQL
    • PostgreSQL Features
    • How to Install PostgreSQL
    • PostgreSQL Versions
    • PostgreSQL Architecture
    • PostgreSQL GUI
    • PostgreSQL Variables
    • PostgreSQL Data Types
    • PostgreSQL NOT NULL
    • PostgreSQL Integer
    • PostgreSQL Boolean
    • PostgreSQL NULLIF
    • PostgreSQL Administration
    • PostgreSQL Commands
    • PostgreSQL Operators
    • PostgreSQL IN Operator
  • Control Statement
    • PostgreSQL IF Statement
    • PostgreSQL if else
    • PostgreSQL CASE Statement
    • PostgreSQL LOOP
    • PostgreSQL For Loop
    • PostgreSQL While Loop
  • Joins
    • Joins in PostgreSQL
    • PostgreSQL Inner Join
    • PostgreSQL Outer Join
    • LEFT OUTER JOIN in PostgreSQL
    • PostgreSQL FULL OUTER JOIN
    • PostgreSQL LEFT JOIN
    • PostgreSQL Full Join
    • PostgreSQL Cross Join
    • PostgreSQL NATURAL JOIN
    • PostgreSQL UPDATE JOIN
  • Queries
    • PostgreSQL Queries
    • PostgreSQL WHERE Clause
    • PostgreSQL WITH Clause
    • PostgreSQL ORDER BY
    • PostgreSQL ORDER BY Random
    • PostgreSQL GROUP BY
    • PostgreSQL group_concat
    • PostgreSQL HAVING
    • PostgreSQL Recursive Query
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Corporate Training
  • 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

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you
Book Your One Instructor : One Learner Free Class

Let’s Get Started

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

EDUCBA

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

Forgot Password?

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

Special Offer - All in One Data Science Bundle (360+ Courses, 50+ projects) Learn More