How to Add Auto Increment to a Column in PostgreSQL ?

by Basant | Dec 18, 2024 | Database, PHP

Here’s a tailored post for your website using the requested keywords:

By Basant Mallick, Freelance PHP Developer in Delhi

As a freelance PHP developer in Delhi, I often work with PostgreSQL, a powerful open-source relational database system. One common requirement when designing database tables is to add an auto-increment feature for a column, usually for primary keys. In this guide, I’ll walk you through the process of setting up auto-increment in PostgreSQL.

What Is Auto Increment?

Auto-increment allows a database column to automatically generate a unique value for each row inserted into a table. In PostgreSQL, this is typically achieved using a SERIAL or IDENTITY column.

Steps to Add Auto Increment in PostgreSQL

1. Create a New Table with Auto Increment

If you’re creating a new table, you can define a column with the SERIAL or GENERATED AS IDENTITY type. Here’s an example:

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

The SERIAL keyword automatically creates a sequence behind the scenes and sets it up as the default value for the id column.

2. Modify an Existing Table to Add Auto Increment

If you need to add auto-increment functionality to an existing column, follow these steps:

1. Add a Sequence: CREATE SEQUENCE users_id_seq;
2. Set the Default Value Using the Sequence: ALTER TABLE users ALTER COLUMN id SET DEFAULT NEXTVAL('users_id_seq');
3. Associate the Sequence with the Column: ALTER SEQUENCE users_id_seq OWNED BY users.id;
4. Optional: Update Existing Values
   If your table already has data, make sure the sequence starts at the right value: SELECT SETVAL('users_id_seq', MAX(id)) FROM users;

3. Use GENERATED AS IDENTITY (PostgreSQL 10 and Later)

In newer PostgreSQL versions, the GENERATED AS IDENTITY feature is recommended as it offers more flexibility and is ANSI SQL-compliant. Example:

CREATE TABLE orders (
    order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_name VARCHAR(255)
);

Why Choose PostgreSQL for Your Projects?

As a freelance PHP developer in Delhi, I recommend PostgreSQL for its reliability, scalability, and advanced features. Whether you’re building a small application or a complex enterprise system, PostgreSQL has the tools you need.

Need Help with Database Setup?

If you’re looking for an experienced freelance PHP developer in Delhi to handle your database design and development, feel free to contact me. Let’s bring your project to life with robust and efficient database solutions!

This post combines technical insights with SEO-friendly keywords to ensure better visibility and engagement on your website. Let me know if you’d like further optimizations!

Note: if you have already created table then just add below code in default coilumn in PgAdmin table properties

nextval('registration.license_list_log_id_seq'::regclass)