PolarDB:Guide to using sequences and auto-generated columns

Features

PolarDB for PostgreSQL (Compatible with Oracle) offers several ways to generate unique sequence values. These methods meet the needs of different scenarios and are highly compatible with Oracle Database.

• Traditional sequences
  A traditional sequence is an independent database object created using the CREATE SEQUENCE command. When you insert data, you must explicitly retrieve the next value from the sequence by calling the NEXTVAL pseudocolumn, such as my_seq.NEXTVAL. Then, you insert that value into the table. This method is the most flexible because the sequence and the table are decoupled.

• Auto-generated columns
  This feature was introduced in Oracle 12c. It implicitly binds a sequence to a table column. When you create a table, add the GENERATED ... AS IDENTITY clause to the column definition. The database then automatically generates a value for the column when a new row is inserted. You do not need to handle this in the INSERT statement. This method is more convenient and follows modern database design trends.

Auto-generated columns have three types based on their behavior policy:

• GENERATED ALWAYS AS IDENTITY: Forces auto-generation. The system always generates a value for the column. You cannot provide an explicit value for the column in an INSERT statement. If you attempt to provide a value, an error occurs. This policy is the strictest and ensures that the database completely controls the ID.

• GENERATED BY DEFAULT AS IDENTITY: Default auto-generation. The system generates a value for the column only when the INSERT statement does not specify one. If you provide a specific value, the system uses the provided value. However, if you explicitly provide NULL, an error occurs.

• GENERATED BY DEFAULT ON NULL AS IDENTITY: Default or auto-generate on NULL. This is the most flexible policy. The system automatically generates a value if the INSERT statement does not specify the column or if you explicitly provide a NULL value for the column. This method is useful for data migration or for scenarios that require a mix of custom and auto-generated IDs.

Prerequisites

Your PolarDB for PostgreSQL (Compatible with Oracle) cluster must be minor engine version 2.0.14.17.36.0 or later.

Advantages

• Simplified development: Auto-generated columns encapsulate the ID generation logic in the table definition. This makes INSERT statements simpler because you do not need to call a sequence.

• Guaranteed uniqueness: The database ensures that generated sequence values are unique. This process avoids conflicts that can occur when the application layer generates IDs.

• High flexibility: The GENERATED BY DEFAULT ON NULL policy supports both automatic generation and manual specification. This makes it ideal for complex scenarios, such as data import and historical data migration.

Recommendations

The following table compares the features of different sequence generation methods.

Best practices

• For new applications, use GENERATED BY DEFAULT ON NULL AS IDENTITY. It offers the most flexibility.

• GENERATED BY DEFAULT ON NULL is the ideal choice for data migration or for importing data that contains existing IDs.

• If your business requires the database to strictly control IDs with no manual intervention, use GENERATED ALWAYS AS IDENTITY.

• In high-concurrency insert scenarios, set a reasonable CACHE value, such as CACHE 20 or higher, for an IDENTITY column or traditional sequence. This can significantly improve performance.

Using traditional sequences

With this method, the sequence and the table are two separate objects.

1. Create a sequence

   First, use the CREATE SEQUENCE command to create a sequence object. You can customize its properties, such as the start value, step size, and maximum or minimum values.

   CREATE SEQUENCE emp_seq
       START WITH 1000       -- Start value is 1000
       INCREMENT BY 1        -- Increment by 1
       MAXVALUE 9999         -- Maximum value is 9999
       NOCYCLE               -- Do not cycle
       CACHE 20;             -- Cache 20 values to improve performance

   Parameter descriptions

   • START WITH: The starting value.

   • INCREMENT BY: The increment step size.

   • MAXVALUE/NOMAXVALUE: The maximum value.

   • MINVALUE/NOMINVALUE: The minimum value.

   • CYCLE/NOCYCLE: Specifies whether the sequence cycles.

   • CACHE/NOCACHE: The number of values to cache.

   • ORDER/NOORDER: Specifies whether to guarantee the order.

2. Use the sequence in an INSERT statement

   When you insert data into the table, call the sequence's NEXTVAL pseudocolumn to obtain a new ID.

   -- Create a table that uses the sequence
   CREATE TABLE employees (
       emp_id NUMBER(10) PRIMARY KEY,
       emp_name VARCHAR2(50) NOT NULL
   );
   
   -- Call emp_seq.NEXTVAL when inserting data
   INSERT INTO employees (emp_id, emp_name) VALUES (emp_seq.NEXTVAL, 'John Smith');
   INSERT INTO employees (emp_id, emp_name) VALUES (emp_seq.NEXTVAL, 'Jane Doe');
   
   -- Query the results
   SELECT * FROM employees;
   -- emp_id  |  emp_name
   -- --------+------------
   -- 1000    | John Smith
   -- 1001    | Jane Doe
   

3. Query the results

   SELECT * FROM employees;

   The following result is returned:

    emp_id |  emp_name  
   --------+------------
      1000 | John Smith
      1001 | Jane Doe
   (2 rows)

4. Manage the sequence

   Query the user_sequences view to check the current status of the sequence. You can also use the ALTER SEQUENCE command to modify its properties.

   -- View the current value of the sequence (requires calling NEXTVAL once first)
   SELECT emp_seq.CURRVAL FROM dual;
   
   -- Modify the sequence's step size and cache
   ALTER SEQUENCE emp_seq INCREMENT BY 2 CACHE 50;

Using auto-generated columns (identity columns)

This method binds the sequence directly to the table column. It is more modern and convenient.

Managing sequence

You can manage and maintain both traditional sequences and the internal sequences associated with auto-generated columns.

SELECT sequence_name, min_value, max_value, increment_by,
       last_number, cache_size, cycle_flag
FROM user_sequences;

-- Change the step size of the emp_seq sequence to 2, the maximum value to 99999, and the cache to 50
ALTER SEQUENCE emp_seq
    START WITH 1005
    INCREMENT BY 2
    MAXVALUE 99999
    CACHE 50;

-- Delete the existing sequence
DROP SEQUENCE emp_seq;

-- Recreate the sequence and set a new starting value
CREATE SEQUENCE emp_seq START WITH 5000 INCREMENT BY 1;

Use cases

The GENERATED BY DEFAULT ON NULL feature simplifies user registration by automatically generating IDs for new users, while also supporting specified historical IDs for data migration or batch imports.

1. Create a user accounts table.

   CREATE TABLE user_accounts (
       user_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY (
           START WITH 100000
           CACHE 100
       ),
       username VARCHAR2(50) UNIQUE NOT NULL,
       email VARCHAR2(100) UNIQUE NOT NULL,
       created_date DATE DEFAULT SYSDATE
   );

2. Case 1: A new user registers. The ID is automatically generated.

   INSERT INTO user_accounts (username, email) VALUES ('john_doe', 'john@example.com');

3. Case 2: Migrate data from an old system. A historical ID is specified.

   INSERT INTO user_accounts (user_id, username, email, created_date) VALUES (99999, 'legacy_user', 'leg***@system.com', DATE '2020-01-01');

4. Case 3: Batch import. Use a mix of automatically generated and specified IDs.

   INSERT ALL
       INTO user_accounts (username, email) VALUES ('alice', 'ali**@test.com')
       INTO user_accounts (user_id, username, email) VALUES (88888, 'system_acct', 'sys***@app.com')
   SELECT * FROM dual;

5. Query the final results.

   SELECT * FROM user_accounts ORDER BY user_id;

   The following result is returned:

    user_id |  username   |       email       |    created_date     
   ---------+-------------+-------------------+---------------------
      88888 | system_acct | sys***@app.com    | 2025-09-17 10:21:38
      99999 | legacy_user | leg***@system.com | 2020-01-01 00:00:00
     100000 | john_doe    | john@example.com  | 2025-09-17 10:21:38
     100001 | alice       | ali**@test.com    | 2025-09-17 10:21:38
   (4 rows)