How to Setup PostgreSQL Auto-increment Primary Key?

There could be occasions where you build and maintain tables in PostgreSQL when you want particular values for a column that is generated on request. This would be particularly true for “id” columns that act as the table's primary key. Luckily, the SERIAL pseudo-type allows making an auto-incrementing integer series convenient. A series is a type of database object in PostgreSQL that produces a series of indexes or integers. A PostgreSQL sequence produces a string of distinct integers, making it suitable to be used as a primary key when generating a new table. We'll demonstrate to you what auto-increment menas in PostgreSQL and we will use the SERIAL pseudo-type throughout this guide.

Syntax:

The general syntax for creating the auto-increment primary key is as follows:

>> CREATE TABLE table_name ( id SERIAL );

Let us now glance at the CREATE TABLE declaration in more detail:

• PostgreSQL generates a series entity first. It produces the next value in the series and sets it as the field's default reference value.
• PostgreSQL applies the implicit restriction NOT NULL to an id field since a series produces numeric values.
• The id field will be allocated as the holder of the series. If the id field or the table itself is omitted, the sequence will be discarded.

To get the concept of auto-increment, please ensure PostgreSQL is mounted and configured on your system before continuing with the illustrations in this guide. Open the PostgreSQL command-line shell from the desktop. Add your server name on which you want to work on, otherwise left it to default. Write the database name that lies in your server on which you want to work on. If you don't want to change it then left it as default. We will be using the “test” database, that's why we have added it. You can also work on the default port 5432, but you can also change it. In the end, you have to provide the username for the database you choose. Leave it to default if you don't want to change it. Type your password for the selected username and hit “Enter” from the keyboard to start using the command shell.

Using SERIAL Keyword as Data Type:

When we create a table, we usually don't add the keyword SERIAL in the primary column field. This means we have to add the values to the primary key column while using the INSERT statement. But when we use the keyword SERIAL in our query while creating a table, we shouldn't need to add primary column values while inserting the values. Let's take a glance at it.

Example 01:

Create a table “Test” with two columns “id” and “name”. The column “id” has been defined as the primary key column as its datatype is SERIAL. On the other hand, the column “name” is defined as the TEXT NOT NULL data type. Try the below command to create a table and the table will be created efficiently as seen in the image below.

>> CREATE TABLE Test( id SERIAL PRIMARY KEY, name TEXT NOT NULL);

Let's insert some values to the column “name” of the newly created table “TEST”. We will not add any value to the column “id”. You can see that the values have been successfully inserted using the INSERT command as stated below.

>> INSERT INTO Test(name) VALUES ('Aqsa'), ('Rimsha'), ('Khan');

It's time to check the records of the table 'Test'. Try the below SELECT instruction in the command shell.

>> SELECT * FROM Test;

From the output below, you can notice that the column “id” has automatically got some values in it even though we haven't added any values from the INSERT command because of the datatype SERIAL we have specified for the column “id”. This is how the datatype SERIAL works on its own.

Example 02:

Another way to check the SERIAL data type column's value is by using the RETURNING keyword in the INSERT command. The declaration below creates a new line in the “Test” table and yields the value for the “id” field:

>> INSERT INTO Test(name) VALUES ('Hassam') RETURNING id;

By checking the records of the table “Test” using the SELECT query, we got the below output as displayed in the image. The fifth record has been efficiently added to the table.

>> SELECT * FROM Test;

Example 03:

The alternative version of the above insert query is using the DEFAULT keyword. We will be using column “id” name in the INSERT command, and in the VALUES section, we will give it the DEFAULT keyword as its value. The below query will work the same upon execution.

>> INSERT INTO Test(id, name) VALUES (DEFAULT, 'Raza');

Let's check the table again using the SELECT query as follows:

>> SELECT * FROM Test;

You can see from the output below, the new value has been added while the column “id” has been incremented by default.

Example 04:

The sequence number of the SERIAL column field can be found in a table in PostgreSQL. The method pg_get_serial_sequence() is used to accomplish this. We have to use the currval() function along with the pg_get_serial_sequence() method. In this query, we will be providing the table name and its SERIAL column name in the parameters of the function pg_get_serial_sequence(). As you can see, we have specified table “Test” and column “id”. This method is used in the below query example:

>> SELECT currval(pg_get_serial_sequence('Test', 'id'));

It's worth noting that our currval() function helps us to extract the sequence's most recent value, which is “5”. The below picture is an illustration of what the performance could look like.

Conclusion:

In this guide tutorial, we have demonstrated how to use the SERIAL pseudo-type to auto-increment in PostgreSQL. Using a series in PostgreSQL, it's simple to build an auto-incrementing set of numbers. Hopefully, you'll be able to apply the SERIAL field to the table descriptions using our illustrations as a reference.