How to make id as auto increment in postgrey SQL?

just add below code in column properties id Default input

Method: 1
nextval(‘form_submissions_id_seq’::regclass)

Method: 2 Via SQL Query

ALTER TABLE states
ADD COLUMN id SERIAL PRIMARY KEY;
Note: if you already have column id previous please delete before above running sql

How to Make ID as Auto Increment in PostgreSQL

When building a database in PostgreSQL, it’s common to want the ID field of a table to automatically increment whenever a new record is added. This ensures that each record has a unique identifier without needing manual input. Thankfully, PostgreSQL provides an easy and efficient way to set up auto-incrementing IDs using sequences.

In this blog, we’ll guide you through the process of creating an auto-incrementing ID field in PostgreSQL and explain how to use the nextval() function to set it up.

What Is an Auto Increment ID?

An auto-increment ID is a column in a database table where the value of the ID is automatically generated and incremented whenever a new row is inserted into the table. This is especially useful for primary keys, ensuring that each record has a unique identifier.

In PostgreSQL, auto-increment behavior is achieved by using a sequence. A sequence is a special kind of object in PostgreSQL that generates a series of unique numbers. You can associate this sequence with a table’s ID column so that the ID values are automatically generated in sequence.

How to Create Auto Increment ID in PostgreSQL

Follow these steps to create a table with an auto-incrementing ID in PostgreSQL.

1. Create a New Table with an Auto-Incrementing ID

The easiest way to create an auto-incrementing ID column in PostgreSQL is to use the SERIAL data type. This automatically creates an integer column that auto-increments with each insert.

CREATE TABLE form_submissions (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    submission_date TIMESTAMP
);

In the example above:

• id SERIAL automatically creates a sequence called form_submissions_id_seq and assigns it to the id column.
• Every time a new row is inserted, the id value will increment automatically.

2. Manually Setting the Auto-Increment ID Using nextval()

In some cases, you might prefer to manually set the sequence for the auto-increment functionality, or if you are working with an existing table that doesn’t have the SERIAL data type. Here’s how you can make an existing id column auto-increment using the nextval() function.

To do this, you would need to modify the DEFAULT value of the id column to use the nextval() function.

ALTER TABLE form_submissions
ALTER COLUMN id SET DEFAULT nextval('form_submissions_id_seq'::regclass);

Explanation:

• nextval('form_submissions_id_seq'::regclass) is a function call that retrieves the next value from the sequence.
• 'form_submissions_id_seq' is the name of the sequence that generates the values. When you use SERIAL, PostgreSQL automatically generates the sequence for you, and the sequence name is typically <table_name>_<column_name>_seq.
• By setting the default value of the id column to nextval(), PostgreSQL will automatically assign a unique incremented value every time a new row is inserted into the table.

3. Inserting Data into the Table

Once the auto-increment feature is set up, you can insert data without needing to specify the id value, as PostgreSQL will take care of it automatically.

INSERT INTO form_submissions (name, email, submission_date)
VALUES ('John Doe', '[email protected]', '2025-01-28');

The id will be automatically generated, and in this case, it might be 1 for the first record.

4. Check the Table Data

To verify that the id is auto-incrementing correctly, you can run a simple SELECT query to see the data:

SELECT * FROM form_submissions;

You should see the id values starting from 1 and incrementing for each new row added.

Conclusion

Setting up an auto-increment ID in PostgreSQL is easy and helps ensure that every record in your table has a unique identifier. Whether you use the SERIAL data type for automatic setup or manually modify the column’s DEFAULT value with nextval(), PostgreSQL makes it simple to manage ID auto-incrementation.

Remember, to use the auto-increment functionality with an existing column, simply modify the column’s DEFAULT property to use the nextval() function as shown below:

ALTER TABLE form_submissions
ALTER COLUMN id SET DEFAULT nextval('form_submissions_id_seq'::regclass);

With these steps, you’ll be able to maintain a clean and consistent way of generating unique IDs for every new record added to your PostgreSQL database.

Happy coding!