In databases such as PostgreSQL or SQL Server, sequences and identity columns can generate unique values, but their purpose and behavior are different. 1. The sequence is an independent object that can be called manually to generate numbers, suitable for custom step sizes, pre-generated IDs, or multi-table shared ID pools; 2. The identification column is part of the table, and the value is automatically obtained from the hidden sequence when a new row is inserted, simplifying primary key management; 3. If you need to control the ID generation logic, you should use the sequence, while the conventional scenario recommends identification columns to avoid conflicts and errors; 4. Note that there may be a number gap between both, and the starting value can be adjusted through ALTER SEQUENCE, while paying attention to permission management and cleaning up orphaned sequences.

In databases like PostgreSQL or SQL Server, sequences and identity columns are tools that help generate unique values automatically — typically for primary keys. They might look similar at first glance, but they serve slightly different purposes and behave differently depending on the system you're using.

Here's how to actually work with them in real scenarios.

What's the Difference Between Sequences and Identity Columns?

A sequence is a standalone object in the database that generates a sequence of numbers. You can use it manually whenever you want a new number, not just when inserting into a table.

An identity column , on the other hand, is part of a table definition and automatically pulls its next value from a hidden (or sometimes visible) sequence when a new row is inserted without specifying that column.

For example, if you define a column like this:

 id INT GENERATED ALWAYS AS IDENTITY

You don't need to insert anything into id — the database handles it automatically by tying it to a sequence behind the scenes.

How to Use a Sequence Manually

If you want more control over value generation, creating and using a sequence directly makes sense.

To create one:

 CREATE SEQUENCE my_sequence START WITH 100 INCREMENT BY 10;

Then, when inserting data:

 INSERT INTO my_table (id, name)
VALUES (NEXT VALUE FOR my_sequence, 'Alice');

This is useful when:

• You need custom increments or starting points.
• You want to pre-generate IDs before inserting related data.
• You're managing multiple tables that share the same ID pool.

Also, sequences can be reused across multiple tables — handy for systems where different entities need globally unique identifiers within an application.

When to Stick With Identity Columns

Most of the time, identity columns are easier and safer to use because they tie directly to a specific column and handle everything automatically.

Here's how to define one:

 CREATE TABLE users (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username TEXT
);

Now, every time you insert a new user:

 INSERT INTO users (username) VALUES ('john_doe');

The id gets filled in automatically. No need to worry about missing values or conflicts unless you're doing something unusual like bulk imports or syncing data between databases.

One thing to watch: if you try to insert a value into an identity column when it's set to GENERATED ALWAYS , you'll get an error. If you need flexibility to override sometimes, define it as GENERATED BY DEFAULT .

Common Gotchas and Fixes

• Gaps in numbering : Both sequences and identity columns can skip numbers due to rollbacks or failed inserts. That's normal — don't expect perfectly continuous IDs unless you manage that yourself.

• Resetting the counter : If your app expects the next value to start from a specific number, you can reset it:

   ALTER SEQUENCE my_sequence RESTART WITH 500;

  Or for identity columns:

   ALTER SEQUENCE users_id_seq RESTART WITH 200;

• Permissions : Make sure your application user has access to the sequence, especially if you're using it manually. Otherwise, you'll hit permission errors during inserts.

• Dropping or renaming : If you drop a table, the associated sequence usually sticks around unless explicitly removed. Be careful not to leave orphaned sequences behind.

---

So yeah, sequences give you more control while identity columns simplify auto-incrementing fields. Depending on your needs, you might use one or both.

Basically that's it.

The above is the detailed content of Working with SQL Sequences and Identity Columns. For more information, please follow other related articles on the PHP Chinese website!