Hologres:Default values

SQL scenarios

In SQL scenarios, if a default value is configured for a field and no value is specified for the field in data write or update operations, the system automatically fills the default value for the field. The following examples are for your reference.

In these examples, a table named default_test is created and default values are configured for Column a and Column b. Sample code:

BEGIN;
CREATE TABLE default_test (
    a int NOT NULL DEFAULT 2023,
    b timestamptz DEFAULT '2023-11-17 10:11:33.276+08',
    PRIMARY KEY (a)
);
COMMIT;

• Example 1: Specify values for Column a and Column b. The specified values overwrite the default values.

  -- Specify values for Column a and Column b for which default values are configured.
  INSERT INTO default_test VALUES (1, NULL);
  -- Query data from the table.
  SELECT * FROM default_test;

  The following result is returned:

  a	b
  1	\N

• Example 2: Do not specify a value for Column b. The system automatically fills the default value for Column b.

  -- Do not specify a value for Column b.
  INSERT INTO default_test (a) VALUES (2);
  -- Query data from the table.
  SELECT * FROM default_test;

  The following result is returned:

  a	 b
  1	 \N
  2	 2023-11-17 10:11:33.276+08

• Example 3: Execute the INSERT ON DO NOTHING statement. In this statement, no value is specified for Column a that serves as the primary key column. In this case, the system writes the specified data to Column b and automatically fills the default value in Column a.

  -- Execute the INSERT ON DO NOTHING statement. In this statement, no value is specified for Column a that serves as the primary key column. In this case, the system writes the specified data to Column b and automatically fills the default value in Column a. 
  INSERT INTO default_test (b) VALUES ('2023-11-18 10:11:33.276+08') ON CONFLICT (a) DO NOTHING;
  -- Query data from the table.
  SELECT * FROM default_test;

  The following result is returned:

  a	b
  2	2023-11-17 10:11:33.276+08
  2023	2023-11-18 10:11:33.276+08
  1	\N

• Example 4: Execute the INSERT ON CONFLICT DO UPDATE statement. In this statement, values are specified for Column a and Column b. The specified values overwrite the default values.

  -- Execute the INSERT ON CONFLICT DO UPDATE statement. In this statement, values are specified for Column a and Column b. The specified values overwrite the default values. 
  INSERT INTO default_test (a, b)VALUES (2,null ) ON CONFLICT (a) DO UPDATE SET (a,b) = ROW(EXCLUDED.*);
  -- Query data from the table.
  SELECT * FROM default_test;

  The following result is returned:

  a	b
  1	\N
  2023	2023-11-18 10:11:33.276+08
  2	\N

• Example 5: Execute the INSERT ON CONFLICT DO UPDATE statement. In this statement, a value is specified for Column b and the specified value overwrites the default value. The value in Column a is not updated.

  -- Execute the INSERT ON CONFLICT DO UPDATE statement. In this statement, a value is specified for Column b and the specified value overwrites the default value. The value in Column a is not updated. 
  INSERT INTO default_test (a, b) VALUES (2023,null)ON CONFLICT (a) DO UPDATE SET b = EXCLUDED.b;
  -- Query data from the table.
  SELECT * FROM default_test;

  The following result is returned:

  a	b
  2	\N
  1	\N
  2023	\N

Use Flink to write data to Hologres

JDBC mode

When you use Flink to write data to Hologres, the following rules apply to fields for which no values are specified:

• Nullable fields

  • If no default value is configured for a field, the system fills Null for the field.

  • If a default value is configured for a field and the mutatetype parameter is set to InsertOrUpdate, the system fills the default value for the field. Otherwise, the system fills Null for the field.

• Non-null fields

  • If no default value is configured for a field, the following rules apply:

    • If the EnableDefaultForNotNullColumn parameter is set to false, the system reports an error message rather than filling a default value.

    • If the EnableDefaultForNotNullColumn parameter is set to true, Holo Client fills the data type-based default value. Example: an empty string ("").

  • If a default value is configured for a field, the following rules apply:

    • If the EnableDefaultForNotNullColumn parameter is set to false, the system fills the configured default value for the field. In versions earlier than Hologres V2.0, fixed plans may fail to be used and performance issues may occur.

      Note

      You can manually upgrade your Hologres instance or join the Hologres DingTalk group to apply for an upgrade. For more information, see the "Manual upgrade" section in Upgrade instances and Obtain online support for Hologres.

    • If the EnableDefaultForNotNullColumn parameter is set to true, Holo Client fills the data type-based default value.

Fixed copy mode

Only the UPDATE and IGNORE operations can be performed in this mode. If you specify to update specific fields, only values of the specified fields are updated. The values of other fields remain unchanged. If you want to achieve the REPLACE effect, you must declare all fields in the Flink DDL statement. The following rules apply to fields for which no values are specified:

• Nullable fields

  • If no default value is configured for a field, the system fills Null for the field.

  • If a default value is configured for a field, the system fills the default value for the field.

• Non-null fields

  • If no default value is configured for a field, the system reports an error message.

  • If a default value is configured for a field, the system fills the default value for the field.

BulkLoad mode (HQEcopy)

The UPDATE operation cannot be performed in this mode. If you specify values only for specific fields when you write data to Hologres, the following rules apply to fields for which no values are specified:

• Nullable fields

  • If no default value is configured for a field, the system fills Null for the field.

  • If a default value is configured for a field, the system fills the default value for the field.

• Non-null fields

  • If no default value is configured for a field, the system reports an error message.

  • If a default value is configured for a field, the system fills the default value.

Use Data Integration of DataWorks to write data to Hologres

DataX-based batch synchronization

You can use DataX of Data Integration to perform batch synchronization from a table or a database. For more information, see Hologres data source. When you use Data Integration of DataWorks, the JDBC mode is automatically used. If you specify values only for specific fields when you write data to Hologres, the following rules apply to fields for which no values are specified:

• Nullable fields

  • If no default value is configured for a field, the system fills Null for the field.

  • If a default value is configured for a field, the following rules apply:

    • If the conflictMode parameter is set to Replace, the system fills Null for the field.

    • If the conflictMode parameter is set to Update, the system fills the default value for the field.

• Non-null fields

  • If no default value is configured for a field, the following rules apply:

    • If the default.enable parameter is set to the default value true, Holo Client fills the data type-based default value for the field. Example: an empty string ("") or 0.

    • If the default.enable parameter is set to false, the system reports an error message.

  • If a default value is configured for a field, the following rules apply:

    • If the default.enable parameter is set to the default value true, the system fills the default value for the field.

    • If the default.enable parameter is set to false, the system also fills the default value for the field. In versions earlier than Hologres V2.0, fixed plans may fail to be used, and performance issues may occur.

StreamX-based real-time synchronization

You can use StreamX of Data Integration to perform real-time synchronization from a table. For more information, see Hologres data source. When you use Data Integration of DataWorks, the JDBC mode is automatically used. If you specify values for specific fields when you write data to Hologres, the following rules apply to fields for which no values are specified:

• Nullable fields

  • If no default value is configured for a field, the system fills Null for the field.

  • If a default value is configured for a field, the following rules apply:

    • If the conflictMode parameter is set to Replace or Ignore, the system fills Null for the field.

    • If the conflictMode parameter is set to Update, the system fills the default value for the field.

• Non-null fields

  • If no default value is configured for a field, the following rules apply:

    • If the default.enable parameter is set to the default value true, Holo Client fills the data type-based default value for the field. Example: an empty string ("") or 0.

    • If the default.enable parameter is set to false, the system reports an error message.

  • If a default value is configured for a field, the following rules apply:

    • If the default.enable parameter is set to the default value true, the system fills the default value for the field.

    • If the default.enable parameter is set to false, the system also fills the default value for the field. In versions earlier than Hologres V2.0, fixed plans may fail to be used, and performance issues may occur.

      Note

      You can manually upgrade your Hologres instance or join the Hologres DingTalk group to apply for an upgrade. For more information, see the "Manual upgrade" section in Upgrade instances and Obtain online support for Hologres.

Mappings between the default values and data types of Holo Client

If you use Flink or Data Integration of DataWorks that is connected by using the Hologres connector to write data to Hologres in JDBC mode, Holo Client is used. If you specify values for specific fields when you write data, the default.enable parameter is automatically set to true (enableDefaultForNotNullColumn). If no value is specified for a field and no default value is configured for the field, Holo Client fills the data type-based default value. The following table describes mappings between the default values and data types.