Hologres:PostgreSQL SERIAL

Data types

SMALLSERIAL is not supported in Hologres.

Limitations

• No custom parameters: You cannot change the increment step size or the default starting value. Both default to 1.

• Superuser initialization required: The first time you use SERIAL or BIGSERIAL in a database, a superuser must create a table with a serial column — for example, CREATE TABLE test(a SERIAL);. This initializes the sequence infrastructure that all subsequent users depend on. SERIAL support is database-scoped, so the superuser must repeat this step in each new database.

• Sequence gaps: For insert on conflict operations based on a primary key, strict continuity is not guaranteed. If you require strict ordering, manually set the starting value of the sequence. Use restart with to reset the starting value if needed.

• Flink write support: When using Flink for data writes, only Java Database Connectivity (JDBC) mode and data integration (insert into) mode support SERIAL and BIGSERIAL. DataHub does not support writing to these types.

• Write performance: Serial writes hold a global lock to generate the next sequence value, which adds overhead and reduces write throughput. In performance-sensitive scenarios, use batch inserts instead of single-row inserts. If your SQL is compatible with Fixed Plan, enable the following GUC parameter to allow serial fields to use Fixed Plan:

  -- Enable Fixed Plan writes for tables that contain serial columns (database level)
  ALTER DATABASE <user_db> SET hg_experimental_enable_fixed_dispatcher_autofill_series = on;

  Serial writes introduce additional global lock overhead, which significantly impacts write performance. Use this feature with caution in performance-sensitive scenarios.

• `restart with` only: Modifying serial parameters is supported from Hologres V0.10 and later, and only the restart with clause is supported.

Create an auto-increment field

The following examples use SERIAL. The same syntax applies to BIGSERIAL.

CREATE TABLE tablename (
    colname SERIAL
);

-- Create a table with a serial primary key
CREATE TABLE IF NOT EXISTS test_tb (id SERIAL PRIMARY KEY, f1 TEXT);

-- Insert rows — omit id, it auto-increments
INSERT INTO test_tb (f1) VALUES ('1');
INSERT INTO test_tb (f1) VALUES ('2');
INSERT INTO test_tb (f1) VALUES ('3');

-- Query results ordered by id
SELECT * FROM test_tb ORDER BY id ASC;

package test;

import java.sql.*;

public class HoloSerial {

    // Create a table with a serial primary key
    private static void init(Connection conn) throws Exception {
        try (Statement stmt = conn.createStatement()) {
            stmt.execute("DROP TABLE IF EXISTS test_tb;");
            stmt.execute("CREATE TABLE IF NOT EXISTS test_tb (id SERIAL PRIMARY KEY, f1 TEXT);");
        }
    }

    // Insert 100 rows and query results
    private static void testSerial(Connection conn) throws Exception {
        try (PreparedStatement stmt = conn.prepareStatement("INSERT INTO test_tb (f1) VALUES (?)")) {
            for (int i = 0; i < 100; ++i) {
                stmt.setString(1, String.valueOf(i + 1));
                int affectedRows = stmt.executeUpdate();
                System.out.println("Affected rows: " + affectedRows);
            }
        }

        try (PreparedStatement stmt = conn.prepareStatement("SELECT * FROM test_tb ORDER BY id ASC")) {
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    String res = rs.getObject(1).toString() + "\t" + rs.getObject(2).toString();
                    System.out.println(res);
                }
            }
        }
    }

    public static void main(String[] args) throws Exception {
        Class.forName("org.postgresql.Driver").newInstance();
        String host = "<endpoint>:<port>";  // Replace with your Hologres endpoint
        String db = "<database>";           // Replace with your database name
        String user = "<username>";
        String password = "<password>";
        String url = "jdbc:postgresql://" + host + "/" + db;
        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            init(conn);
            testSerial(conn);
        }
    }
}

Reset the sequence start value

When you create a table with a SERIAL column, Hologres automatically generates a sequence named schema_name.tablename_columnname_seq. Use ALTER SEQUENCE with the restart with clause to change the starting value.

1. Find the sequence name.

   SELECT table_name, column_name, column_default
   FROM information_schema.columns
   WHERE table_schema = 'ods'
     AND table_name = 'test_tb'
     AND column_name = 'id';

   The query results look similar to the following figure. The value in the column_default field is enclosed in single quotation marks. The part inside the quotes — for example, ods.test_tb_id_seq — is the sequence name.

2. Reset the starting value.

   ALTER SEQUENCE ods.test_tb_id_seq RESTART WITH 100;

   Replace ods.test_tb_id_seq with the actual sequence name and 100 with the value you want. Insert a row after resetting to verify that the new value takes effect.

FAQ

Why do I get a "permission denied" error when creating a serial table?

ERROR: permission denied schema hologres

A superuser has not yet initialized SERIAL support in this database. Ask a superuser to run the following statement once:

CREATE TABLE test (a SERIAL);

This creates the required sequence infrastructure. After that, all users with appropriate table-creation permissions can create serial tables.

Why do I get a duplicate key error when I drop and recreate a serial table in the same transaction?

failed: error: duplicate key value violates unique constraint "hg_table_properties_pkey"

Dropping and immediately recreating a SERIAL or BIGSERIAL table within a single transaction is not supported. Split the DROP TABLE and CREATE TABLE statements into separate transactions.