Defining: pgAdmin Auto Increment

What is pgAdmin Auto Increment?

pgAdmin is the most popular Postgres Open Source management tool. In a relational database like PostgreSQL, it is crucial to provide a primary key in each table. Therefore, we must ensure that each table contains an essential primary key column. There are essentially two ways to do this in Postgres.

1. Using the Serial data type
2. With a Custom Sequence

Defining the pgAdmin Auto Increment Primary Key with the Serial Datatype

Postgres data types include smallserial, serial, and bigserial. These are not true types, but they are comparable to the AUTO INCREMENT property supported by some databases.

Serial columns must now be specified in the same way as other data types if they are to have a unique constraint or be a primary key. Integer columns are generated by the Serial type. Bigserial produces a bigint column, whereas Smallserial generates a smallint column. The syntax looks like this:

CREATE TABLE tablename (
   colname SERIAL
);

We can now examine an instance to better comprehend pgAdmin auto-increment.

1. The first step is to create a table with table name:

testdb=# CREATE TABLE STUDENT(
   ID  SERIAL PRIMARY KEY,
   SNAME           TEXT      NOT NULL,
   TEACHER         TEXT      NOT NULL,
   MARK            INT       NOT NULL
);

2. Then, Insert values into the table.

INSERT INTO STUDENT (SNAME,TEACHER,MARK)
VALUES ( 'Science', 'Lisa', 50 );

Repetition 3 times. This will result in 3 tuples within the table Subject. The documents will be:

id | sname      | Teacher  | Mark     
----+----------+----------+----------
  1 | Science  |  Lisa    | 50 
  2 | Maths    |  Jiz     | 45     
  3 | English  |  Mark    | 49

Specifying the pgAdmin Auto Increment Primary Key with a Custom Sequence

PostgreSQL provides an additional method for defining the Auto Increment Primary Key. Occasionally, the incremental nature of the SERIAL and BIGSERIAL data types may not meet the requirements. Therefore, we must create a custom SEQUENCE to implement the same auto-incrementing primary key functionality for the column.

Let’s examine an example to better comprehend it.

CREATE SEQUENCE books_sequence
  start 2
  increment 2;

nextval(‘books sequence’) must be used to evaluate the next value of our sequence. Then, it can be used as an id when we INSERT a new record into the books table.

div style=”height: 12px;”>

INSERT INTO books
  (id, title, primary_author)
VALUES
  (nextval('books_sequence'), 'The Hobbit', 'Tolkien');

In this instance, the primary key will increment by two each time a new tuple is inserted, beginning at 100. Consequently, we can tailor the auto-increment option to our requirements.

We can also indicate extreme values with the minvalue and maxvalue options. The CYCLE option permits the sequence to “loop around” when it reaches the maximum value, returning to the initial value and resuming the ascent.

Are you looking for an answer to another query? Contact our technical support team.