Hologres:PostgreSQL SERIAL

Overview of data types

Hologres allows you to create an auto-increment field in a table by using the SERIAL or BIGSERIAL data type. The SERIAL data type indicates that the data type of an auto-increment field is INT4. The BIGSERIAL data type indicates that the data type of an auto-increment field is INT8.

CREATE TABLE tablename (
    colname serial
);

Limits

• You cannot specify extra parameters of the SERIAL and BIGSERIAL data types in Hologres, including the increment step size and the default value. By default, the value of the increment step size is 1 and the default value is 1.
• You cannot use the data whose data type is SMALLSERIAL in Hologres.
• To create a table with fields whose data types are SERIAL and BIGSERIAL for the first time, a superuser must create the table in a database. For example, after the superuser executes the create table test(a serial); statement, other users can create tables with fields whose data types are SERIAL and BIGSERIAL based on their business logic. Tables with fields whose data types are SERIAL and BIGSERIAL are database-level tables. If you switch to another database, the superuser must execute the preceding statement in the new database.
• You cannot write data into fields of the SERIAL and BIGSERIAL data types in Flink and SDK (Fast Write) modes. You can write data into these fields only after the insert into xxx select statement is executed.
• You can modify the parameter of the SERIAL data type in the restart with optional clause only in Hologres V0.10 and later.

Example 1: Execute SQL statements to create an auto-increment field

// Create a table that contains the id and f1 fields. 
create table if not exists test_tb(id serial primary key, f1 text);

// Insert data into the f1 field by executing the INSERT statements. 
insert into test_tb(f1) values('1');
insert into test_tb(f1) values('2');
insert into test_tb(f1) values('3');

// Query the data in the test_tb table and sort the data by the id field in ascending order. 
select * from test_tb order by id asc;

Example 2: Connect to Hologres over JDBC and create an auto-increment field

package test;

import java.sql.*;

public class HoloSerial {

// Create a table that contains the id and f1 fields. 
    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 data into the f1 field. 
    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 affected_rows = stmt.executeUpdate();
                System.out.println("affected rows => " + affected_rows);
            }
        }

// Query the data in the test_tb table and sort the data by the id field in ascending order. 
        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);
                }
            }
        }
    }

// Connect to Hologres over JDBC. 
    public static void main(String[] args) throws Exception {
        Class.forName("org.postgresql.Driver").newInstance();
        String host = "127.0.0.1:13737";
        String db = "postgres";
        String user = "xx";
        String password = "xx";
        String url = "jdbc:postgresql://" + host + "/" + db;
        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            Init(conn);

            TestSerial(conn);
        }
    }
}

Example 3: Modify a parameter of the SERIAL data type

After you create a table by using a parameter of the SERIAL data type, a sequence named schema_name.tablename_columnname_seq is automatically generated. You can execute the ALTER SEQUENCE statement to modify the parameter of the SERIAL data type. The following part describes the procedure:

1. Execute the following statement to query the generated sequence. You must replace table_schema, table_name, and column_name in the statement with the actual values based on your business requirements. The table that is created in Example 1 is used in the following sample statement:

   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 following figure shows the query results.In the query results, the part that is enclosed in single quotation marks (' '), ods.test_tb_id_seq, indicates the sequence name.
2. After you obtain the sequence name, execute the following statement to modify the parameter of the SERIAL data type in the restart with optional clause.

   alter sequence ods.test_tb_id_seq restart with 100

   You can replace ods.test_tb_id_seq and the number in the statement with other values based on your business requirements. After you modify the parameter, insert data into the table to verify the results.