Hologres:FAQ about Hologres SQL statements

What do I do if the following error message is reported: failed to import foreign schema from odps: Can't find file system factory?

• Cause: Hologres does not support queries of MaxCompute external tables.

• Solution: Create a MaxCompute internal table.

What do I do if the following error message is reported: failed to import foreign schema from odps: Authorization Failed: xxx?

• Cause: Your account does not have the required permissions on the MaxCompute table.

• Solution: Grant the required permissions to the user account. For more information, see Manage user permissions by using commands.

What do I do if the following error message is reported: failed to import foreign schema from odps:Table not found -xxx?

• Cause: The table that you want to query does not exist in MaxCompute.

• Solution: Check whether the table exists in MaxCompute.

What do I do if the following error message is reported: Update row with Key (id)=(xxxx) multiple times or duplicate key value violates unique constraint?

• Cause: A uniqueness constraint violation exists. Some primary key values are duplicate when you perform the UPDATE, INSERT ON CONFLICT, or INSERT operation.

• Solution:

  • If the error message is reported when you perform the INSERT operation, use the INSERT INTO xx ON CONFLICT syntax to deduplicate primary key values. For more information, see INSERT ON CONFLICT(UPSERT).

  • If the error message is reported when you use the INSERT INTO xx ON CONFLICT syntax, the data source contains duplicate data. In this case, perform troubleshooting by following the instructions in the "Errors and troubleshooting" section in INSERT ON CONFLICT(UPSERT).

  • Another reason is that Time to live (TTL) is configured for the table. The TTL is expired but the data is not deleted. In this case, use the hg_remove_duplicated_pk function to delete the data. For more information, see the "Errors and troubleshooting" section in INSERT ON CONFLICT(UPSERT).

What do I do if the following error message is reported: ERROR: new row for relation violates partition constraint?

• Cause: A check constraint violation exists. The partition key value of the data record that is written is different from the partition key value specified in the statement. For example, the partition key value specified in the statement is 20240110, but a data record with a different partition key value is written. This results in partition conflicts. Sample statements:

  CREATE TABLE public.tbl_20240110 PARTITION OF public.tbl FOR VALUES IN('20240110');
  
  INSERT INTO public.tbl_20240110 SELECT * FROM odps_tbl where sale_date >'20240110';
  
  
  error: new row for relation "tbl_20240110" violates partition constraint
  

• Solution: Write valid data records to partitions.

What do I do if the following error message is reported: null value in column "xxx" violates not-null constraint?

• Cause: A NOT NULL constraint violation exists. NULL values are written to NOT NULL columns.

• Solution: Remove the NULL values and rewrite data.

What do I do if the following error message is reported: Dispatch query failed: Table not found?

• Cause: The table does not exist. In most cases, this error message is reported when the table is created but the metadata is not updated or if the TRUNCATE or DROP operation is performed on the table during a query.

• Solution: Use the Query Insight feature of HoloWeb to check whether the TRUNCATE or DROP operation is performed on the table during a query. Then, retry the query. For more information, see Query Insight. Do not execute DDL statements that may cause conflicts during queries.

What do I do if the following error message is reported: ERROR: canceling statement due to statement timeout?

• Cause: A timeout period is configured on the client, and the query times out.

• Solution: Optimize the query to shorten the runtime or set the timeout period to a larger value. For more information about how to configure the timeout period, see Manage queries.

What do I do if the following error message is reported: ERROR: Query:[xxx] Get result failed: canceling statement due to user request?

• Cause: The query is canceled. In most cases, this error message is reported if the TRUNCATE or DROP operation is performed on the table.

• Solution: Use the Query Insight feature of HoloWeb to check whether DDL statements that conflict with the query are executed. For more information, see Query Insight. Do not execute DDL statements that may cause conflicts during queries.

What do I do if the following error message is reported: query is cancelled Cannot find index full ID:xxx (table id: x, index id: x) in storages or it is deleting?

• Cause: The query is performed on a table on which the TRUNCATE or DROP operation is performed. As a result, the table ID is changed, and the query is canceled.

• Solution: Use the Query Insight feature to check whether the TRUNCATE or DROP operation is performed during the query. For more information, see Query Insight.

What do I do if the following error message is reported: Feature not supported: insert into parent table?

• Cause: Data cannot be directly written to a parent partitioned table.

• Solution: When you execute the INSERT statement, write data to the specified child partitioned table. For more information, see CREATE PARTITION TABLE.

What do I do if the following error message is reported: SELECT INTO is not supported now?

• Cause: Hologres does not support the SELECT INTO syntax.

• Solution: Execute the INSERT INTO SELECT statement to insert data. For more information, see INSERT.

What do I do if the following error message is reported: ALTER TABLE CHANGE OWNER is not supported in SLPM (Schema-Level Permission Mode)?

• Cause: The ALTER TABLE statement cannot be executed to change the table owner when you use the schema-level permission model (SLPM) for the table.

• Solution: In SLPM, the permissions of table owners are granted to user groups. To revoke permissions from a user, you can remove the user from a user group. For more information, see the "Remove a user from a user group" section in Use the SLPM.

What do I do if the following error message is reported: Feature not supported: INSERT ON CONFLICT RETURNING?

• Cause: Hologres does not support the INSERT ON CONFLICT RETURNING syntax.

• Solution: Use INSERT ON CONFLICT. For more information, see INSERT ON CONFLICT(UPSERT).

What do I do if the following error message is reported: ERROR: INSERT in ddl transaction is not supported now?

• Cause: The INSERT operation is not supported in transactions. Sample code:

  BEGIN;
  INSERT xxx
  commit;
  ERROR: INSERT in ddl transaction is not supported now

• Solution: Perform the INSERT operation outside a transaction, or start a transaction that supports data manipulation language (DML) statements. For more information, see SQL transaction capabilities.

What do I do if the following error message is reported: Creating publication with table that without binlog is not supported now?

• Cause: A publication is created for a table for which binary logging is not enabled.

• Solution: Publications are used to subscribe to binary logs. You can create a publication only for a table for which binary logging is enabled. For more information, see Use JDBC to consume Hologres binary logs.

What do I do if the following error message is reported: Feature not supported: INSERT on conflict contains un-unique column?

• Cause: Non-primary key columns are specified in the CONFLICT clause of the INSERT ON CONFLICT statement.

• Solution: Make sure that only primary key columns are specified in the CONFLICT clause of the INSERT ON CONFLICT statement.

What do I do if the following error message is reported: Feature not supported: UPDATE with shard keys?

• Cause: Hologres does not support the UPDATE operation on primary keys or distribution keys.

• Solution: Modify the SQL statement and execute it again.

What do I do if the following error message is reported: time before 1925 not supported?

• Cause: Time-related functions support years from 1925 to 2282 by default. For years beyond the range, an error is reported.

• Solution: If you use a function such as to_char, to_date, or to_timestamp, configure the Grand Unified Configuration (GUC) parameter hg_experimental_functions_use_pg_implementation before the SQL statements that you want to execute to extend the time range. For more information, see Date and time functions.

What do I do if the following error message is reported: Group by key is type of imprecise not supported?

• Cause: The field specified in the GROUP BY clause is of an approximate numeric type.

• Solution: Do not use fields of approximate numeric types, such as FLOAT, in the GROUP BY clause.

What do I do if the following error message is reported: CREATE TABLE is not supported for current instance?

• Cause: A Hologres Shared Cluster instance or a secondary instance is used. Endpoints of Hologres Shared Cluster instances start with hgmc. For example, hgmc-cn-xxwwwkkk. You can view the instance endpoint in the Hologres console. For Hologres Shared Cluster instances, you can create foreign tables but not internal tables. For secondary instances, you can query data but cannot create tables.

• Solution:

  • If you use a Hologres Shared Cluster instance, create a foreign table. For more information, see CREATE FOREIGN TABLE. If you want to create internal tables, use exclusive Hologres instances.

  • If you want to create tables, use the primary instance.

What do I do if the following error message is reported: ALTER TABLE ALTER COLUMN SET TYPE is not supported now?

• Cause: The ALTER TABLE statement cannot be used to modify data types of columns.

• Solution: Create a table and modify data types of columns.

What do I do if the following error message is reported: ERROR: Currently materialized view does not support aggregate on expressions, only support single column?

• Cause: Materialized views that are defined based on a single table do not support expressions.

• Solution: Do not use expressions for materialized views that are defined based on a single table. For more information, see Real-time materialized view.

What do I do if the following error message is reported: column xxx does not exist?

• Cause: The column does not exist.

• Solution: Check the SQL statement.

What do I do if the following error message is reported: Table group xxx does not exist?

• Cause: The table group does not exist.

• Solution: Specify an existing table group when you create a table, or create a table group before you create a table.

What do I do if the following error message is reported: The specified partition does not exist?

• Cause: The partition on which the data write or update operation is performed does not exist.

• Solution: Create the required partition in advance.

What do I do if the following error message is reported: create binlog reader failed: Fail to find any shards, please check if the table group is in current warehouse?

• Cause: The table group does not exist in the warehouse.

• Solution: Check whether the related table group is loaded to the warehouse. If the table group is not loaded to the warehouse, configure and load the table group. For more information, see Manage permissions on table groups that are loaded to virtual warehouses.

What do I do if the following error message is reported: Total memory used by all existing queries exceeded memory limitation?

• Cause: An out of memory (OOM) issue occurs during the query.

• Solution: For more information, see FAQ about OOM.

What do I do if the following error message is reported: internal error: Datasets has different schema?

• Cause: The columns specified in the query statement do not match the columns in the table.

• Solution: Check the SQL statement.

What do I do if the following error message is reported: value too long for type character varying(xxx)?

• Cause: The length of field values of the VARCHAR type exceeds the upper limit.

• Solution: Process the field values or set the upper limit for the length of field values of the VARCHAR type to a larger value. You can also change the field type to TEXT.

What do I do if the following error message is reported: Exceeds the partition limitation of 512, current match xxx partitions?

• Cause: The number of partitions to be queried in a MaxCompute table exceeds 512.

• Solution: Add partition filter conditions or configure the following GUC parameter to adjust the maximum number of partitions allowed in a query. For more information, see FAQ about integration with MaxCompute.

  set hg_foreign_table_max_partition_limit =xx;

What do I do if the following error message is reported: Build desc failed: Exceeds the scan limitation of 200 GB, current scan xxx GB?

• Cause: The amount of data to be scanned in a MaxCompute table exceeds 200 GB.

• Solution:

  • Add filter conditions to reduce the amount of data to be scanned.

  • Import data to a Hologres internal table. No upper limit is imposed on the amount of data to be scanned in a Hologres internal table.

What do I do if the following error message is reported: syntax error at or near "xxxxx"?

• Cause: The syntax of the SQL statement is invalid.

• Solution: Check and modify the syntax of the SQL statement.

What do I do if the following error message is reported: DISTINCT is not implemented for window functions?

• Cause: Hologres does not allow you to use the DISTINCT keyword in window functions.

• Solution: Remove the DISTINCT keyword from window functions.

What do I do if the following error message is reported: ERROR:function xxx does not exist?

• Cause: No extension is created when you use the function, or the function syntax is invalid.

• Solution: Use the function by following the requirements of Hologres.

What do I do if the following error message is reported: ERROR: function jsonb_set(json, text[], jsonb, boolean) does not exist?

• Cause: The data type of a parameter in the JSONB_SET function is invalid.

• Solution: Specify a value of the JSONB data type for the first parameter in the JSONB_SET function. For more information, see JSON and JSONB data types.

What do I do if the following error message is reported: You have NO privilege 'odps:Select' on xxx?

• Cause: Your account does not have query permissions on the MaxCompute table.

• Solution: Grant the required permissions to the user account in the MaxCompute console.

What do I do if the following error message is reported: The sensitive label of column 'xxx' is 2, but your effective label is 0?

• Cause: Your account has query permissions only on specific columns in the MaxCompute table.

• Solution: Grant more permissions to the user account in the MaxCompute console, or query data only from the specific columns. For more information about how to grant permissions, see Manage user permissions by using commands.

What do I do if the following error message is reported: extension "xxxxx" already exists?

• Cause: The extension already exists. You do not need to recreate the extension.

• Solution: Do not create an existing extension.

What do I do if the following error message is reported: invalid input syntax for type numeric: \"\"?

• Cause: Fields of the NUMERIC type contain dirty data and do not comply with the specifications of the NUMERIC data type.

• Solution: Handle the dirty data.

What do I do if the following error message is reported: invalid input syntax for integer: xxx?

• Cause: Fields of the INT type contain dirty data and do not comply with the specifications of the INT data type.

• Solution: Handle the dirty data.

What do I do if the following error message is reported: missing data for column "xxx". failed to query next?

• Cause: The format of files or data on which the COPY operation is performed is invalid. In most cases, this error message is reported when the source data contains a delimiter, such as a space, that is specified in the COPY statement. As a result, the number of columns in the source table is different from the number of columns in the destination table.

• Solution: Handle the dirty data.

What do I do if the following error message is reported: ERROR: INSERT has more expressions than target columns?

• Cause: The number of columns to be written by executing the INSERT statement is inconsistent with that in the destination table.

• Solution: Make sure that the number of columns to be written by executing the INSERT statement is consistent with that in the destination table.

What do I do if the following error message is reported: bigint out of range?

• Cause: The length of the data of the BIGINT type exceeds the upper limit.

• Solution: Handle dirty data to ensure that the length of the data of the BIGINT type does not exceed the upper limit.

What do I do if the following error message is reported: numeric field overflow in function round?

• Cause: The values of the fields of the NUMERIC type exceed the specified precision.

• Solution: Handle the dirty data.

What do I do if the following error message is reported: InsertOverwrite insert select table data failed : column a.unsign_type does not exist?

• Cause: The column does not exist, or the parameter value is invalid.

• Solution: Check and modify the syntax of the SQL statement.

What do I do if the following error message is reported: mismatched properties: table orientation is "column" but storage format is "sst"?

• Cause: The table property that is specified during the table creation is invalid. The column-oriented storage mode is specified but SST is selected.

• Solution: Check and modify the syntax of the SQL statement.

What do I do if the following error message is reported: invalid value "" for "yyyy", Value must be an integer?

• Cause: The data does not comply with the specifications of the specific data type.

• Solution: Handle the dirty data.

What do I do if the following error message is reported: invalid byte sequence for encoding "UTF8": 0xe9 0x80?

What do I do if the following error message is reported: relation "xxxx" already exists?

• Cause: The table already exists.

• Solution: Do not create duplicate tables.

What do I do if the following error message is reported: character with byte sequence 0xe4 0x9e 0xab in encoding "UTF8" has no equivalent in encoding "GBK"?

• Cause: Characters encoded in UTF-8 have no equivalent characters in GBK.

• Solution: Handle the dirty data.

What do I do if the following error message is reported: column "xxx" must appear in the GROUP BY clause or be used in an aggregate function?

• Cause: The column must be specified in the GROUP BY clause.

• Solution: Modify the syntax of the SQL statement.

What do I do if the following error message is reported: SET_TABLE_PROPERTY and CREATE TABLE statement are not in the same transaction for table?

• Cause: When you create a table, the CREATE TABLE statement and the CALL statements are not in the same transaction.

• Solution: When you create a table, execute the CREATE TABLE statement and the CALL statements in the same transaction. Sample code:

  begin;
  CREATE TABLE tbl (
   xxxx
  );
  call set_table_property('tbl', 'orientation', 'xx');
  call set_table_property('tbl', 'distribution_key', 'xxx');
  commit;

What do I do if the following error message is reported: column reference "xxx" is ambiguous?

• Cause: In most cases, this error message is reported when a column name in the SQL statement may indicate columns in different tables. For example, in the SELECT id FROM t1 INNER JOIN t2 ON t1.id=t2.id statement, whether id indicates the id column in table t1 or table t2 is not clarified.

• Solution: Modify the syntax of the SQL statement.

What do I do if the following error message is reported: column "xxx" specified more than once?

• Cause: A field is declared multiple times in the table creation statement.

• Solution: Modify the syntax of the SQL statement.

What do I do if the following error message is reported: invalid definition of a numeric type?

• Cause: No precision is specified for fields of the NUMERIC or DECIMAL type during the table creation.

• Solution: Modify the syntax of the SQL statement, and specify precisions for fields of the NUMERIC or DECIMAL type.

What do I do if the following error message is reported: cannot cast type date to integer?

• Cause: Data of the DATE type cannot be converted into the INT type.

• Solution: Modify the syntax of the SQL statement.

What do I do if the following error message is reported: schema "xxxx" does not exist?

• Cause: The schema does not exist.

• Solution: Check whether the syntax of the SQL statement is valid.

What do I do if the following error message is reported: internal error: Connect timeout, err: std_exception: Connection refused?

• Cause: In most cases, this error message is reported because an instance breaks down.

• Solution: Submit a ticket to check the cause of the Hologres instance breakdown.