Two Methods for PostgreSQL Auto Increment
When creating a database, you should have a primary key for each table to ensure that no duplicates appear in the tables. One way of creating the primary keys is using the auto-increment feature for the INT data type. PostgreSQL generates the primary key value for all entries, freeing the developer from the hassle of manually typing the primary.
There are two approaches that you can use to create auto-increment in your PostgreSQL database.
Method 1: Using the SERIAL Pseudo-Type
The default way of creating auto-increment for primary keys in your database is using the SERIEL keyword. When used, the SERIAL pseudo-type creates a sequence to generate the values. It notes the next value in the sequence, and whenever you insert a new value, it gets assigned with a unique identity.
Note that this auto-increment is assigned to an INT data type and gets deleted once the table or the column is dropped.
Here’s the syntax for creating an auto-increment:
For our case, let’s create a table named “details” which contains the “user_id” as our auto-increment and primary key. We also include two other columns.
When we want to insert the values into our table, we only insert for the other columns and leave out the auto-increment column as its values get generated automatically. Here’s an example where we add five entries to our table:
When we use the SELECT statement to check the table records, notice that the “user_id” column contains unique identities which, in our case, are numbers starting from 1. That’s how to do the PostgreSQL auto-increment using the SERIAL pseudo-type.
Method 2: Creating a Sequence
When you use the SERIAL pseudo-type to create the auto-increment values, you don’t control how your unique identities get created. For the previous example, we’ve seen that our unique identities start from 1. However, if you want to control these values, let’s say you want to define at what point to start generating them, you must create a custom sequence.
PostgreSQL has the SEQUENCE option where you can specify the column name to use for the auto-increment and define the start value for the column. For instance, if we want to start the values from 100, we create the following custom sequence:
After creating the sequence, you can now create your table. Note that you must use the same column name where you created a sequence for like the “nextval(‘holder’)” for the created table. That way, PostgreSQL references the sequence to know what order to follow when generating the unique identity for the columns.
We run the following command to create our table:
Proceed to insert the values into the created table for the relevant columns.
When we run the “select” query to list the table entries, we can see that we managed to control our auto-increment. The “user_id” column starts its value at 100 and generates the subsequent values for the other entries that we insert into the table.
That’s the other way of creating the auto-increment for PostgreSQL.
Conclusion
Creating an auto-increment is beneficial for creating uniqueness in your table. When you want to define a primary key for your table, use the auto-increment feature for the INT data type. PostgreSQL generates the values automatically, ensuring that no duplicates exist in your table. We defined how to create the auto-increment for PostgreSQL and provided two examples of the two methods that you can use. Both methods are applicable depending on the scenario that you have. Try them out!
