Here is a handy guide to help you add auto increment to a column in PostgreSQL. Our PostgreSQL Support team is here to lend a hand with your queries and issues.

How to add an auto-increment to an existing column PostgreSQL

If you are looking for a way to auto-increment the values of an existing column in PostgreSQL, you have come to the right place. According to our experts, a sequence is a specific database object used to generate a sequence of integers. In most cases, it is used as the primary key column in a table.

We can easily use the SERIAL pseudo-type to generate a sequence while creating a new table.

Syntax:
CREATE TABLE table_name(
id SERIAL
);

Here, PostgreSQL will create a sequence object. After that, it will set the next value generated by the sequence as the default value for the column.

Furthermore, a NOT NULL constraint is added to the id column since the sequence always generates an integer. Additionally, PostgreSQL will assign the owner of the sequence to the id column.

According to our experts, PostgreSQL offers three types of serial pseudo-types, namely, SMALLSERIAL, SERIAL, and BIGSERIAL

Now, let us look at an example where we create a table called Movies with id column as the SERIAL column:

CREATE TABLE movies(
id SERIAL PRIMARY KEY,
name VARCHAR NOT NULL
);

Suppose we insert three values in this table as seen below:

INSERT INTO movies(name)
VALUES('Casablanca');

INSERT INTO movies(name)
VALUES(‘An Affair to Remember’);

INSERT INTO movies(name)
VALUES(‘Citizen Kane’);

When we run the SELECT command to view the content of the table, we will get the following output:


id | name
----+--------
1 | Casablanca
2 | An Affair to Remember
3 | Citizen Kane


Suppose we insert a new value with the following command:


INSERT INTO movies(name)
VALUES('Breakfast at Tiffanys')
RETURNING id;

This results in the following output:


id
------
4


In the above example, we can see how the values are auto-incremented when we use the Serial pseudo-type. Let us know in the comments if you need further help with adding an auto increment to a column PostgreSQL

[Need assistance with a different issue? Our team is available 24/7.]

Conclusion

To sum up, our Support Techs demonstrated how to auto increment to a column in PostgreSQL