FAQ about Hologres SQL statements

Error: failed to import foreign schema from odps: Can't find file system factory

• Cause: Hologres cannot access the file system of the MaxCompute table due to a configuration error.

• Solution: Verify your foreign server and external table configurations. Alternatively, import the data into a Hologres internal table.

Error: Failed to import foreign schema from ODPS: Authorization Failed: xxx

• Cause: The current account does not have the required permissions on the specified MaxCompute table.

• Solution: Grant the required permissions to the account in MaxCompute. For details, see Manage user permissions by using commands.

Error: failed to import foreign schema from odps:Table not found -xxx

• Cause: The specified table does not exist in MaxCompute.

• Solution: Verify that the table name and project are correct in MaxCompute.

Error: Update row with Key (id)=(xxxx) multiple times or duplicate key value violates unique constraint

• Cause: The data being written contains duplicate primary key values, which violates the unique constraint.

• Solution:

  • If the error occurs for an INSERT statement, use the INSERT INTO xx ON CONFLICT syntax to deduplicate primary keys. For details, see INSERT ON CONFLICT(UPSERT).

  • If this error occurs with an INSERT...ON CONFLICT statement, the source data itself contains duplicate primary keys. For more information, see Common errors.

  • This error can also occur if a table has an expired time to live (TTL) and the expired data has not yet been physically deleted. In this case, use the hg_remove_duplicated_pk function to clean up the duplicate data. For details, see Common errors.

Error: ERROR: new row for relation violates partition constraint

• Cause: The data being inserted into a child partition table does not match the partition value definition. For example, inserting a row with a partition value other than '20240110' into a partition defined for '20240110' causes this error. Example:

  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: Ensure the data you write to a child partition table meets the partition constraint.

Error: null value in column "xxx" violates not-null constraint

• Cause: A NULL value was written to a column that has a NOT NULL constraint.

• Solution: Remove the rows that contain NULL values in that column, and then retry the write operation.

Error: Dispatch query failed: Table not found

• Cause: The table does not exist. This typically occurs when the table was recently created and its metadata has not yet propagated, or when a TRUNCATE or DROP operation was performed on the table during query execution.

• Solution: Use Query Insight in HoloWeb to check for concurrent DDL operations such as TRUNCATE or DROP. Resolve any conflicts, then retry the query. Avoid performing DDL operations on tables that are being actively queried. For details, see Query Insight.

Error: ERROR: canceling statement due to statement timeout

• Cause: The query execution time exceeded the statement timeout configured on the client.

• Solution: Optimize the query to reduce execution time, or increase the timeout duration. For details about timeout settings, see Manage queries.

Error: ERROR: Query:[xxx] Get result failed: canceling statement due to user request

• Cause: The query was canceled, typically because a DROP or TRUNCATE operation was performed on the table during execution.

• Solution: Use Query Insight in HoloWeb to check for conflicting DDL operations. For details, see Query Insight. Avoid running DDL operations during query execution.

Error: query is cancelled Cannot find index full ID:xxx (table id: x, index id: x) in storages or it is deleting

• Cause: A TRUNCATE or DROP operation was performed on one of the queried tables during execution. This changed the table ID, causing the query to be canceled.

• Solution: Use Query Insight to check whether a TRUNCATE or DROP operation ran concurrently with your query. For details, see Query Insight.

Error: Feature not supported: insert into parent table

• Cause: Hologres does not support inserting data directly into a parent partition table.

• Solution: Insert data into a specific child partition table instead. For details, see CREATE PARTITION TABLE.

Error: SELECT INTO is not supported now.

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

• Solution: Use the INSERT INTO SELECT syntax to insert data. For details, see INSERT.

Error: ALTER TABLE CHANGE OWNER is not supported in SLPM (Schema-Level Permission Mode)

• Cause: You cannot use ALTER TABLE to change the table owner in the schema-level permission model (SLPM).

• Solution: In the SLPM, table owners are user groups. To change a user's permissions, remove the user from the user group. For details, see Remove a user from a user group.

Error: Feature not supported: INSERT ON CONFLICT RETURNING

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

• Solution: Use the INSERT ON CONFLICT syntax without the RETURNING clause. For details, see INSERT ON CONFLICT(UPSERT).

Error: ERROR: INSERT in ddl transaction is not supported now

• Cause: By default, Hologres does not support DML statements such as INSERT within a BEGIN...COMMIT transaction block. Example:

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

• Solution: Run the INSERT statement outside the transaction, or enable DML transactions. For details, see SQL transaction capabilities.

Error: Creating publication with table that without binlog is not supported now

• Cause: A publication was created for a table that does not have binlog enabled. Publications require binlog to subscribe to data changes.

• Solution: Enable binlog on the table before creating the publication. You can create publications only for tables with binlog enabled. For details, see Use JDBC to consume Hologres binlogs.

Error: Feature not supported: INSERT on conflict contains un-unique column

• Cause: The conflict target in the INSERT ON CONFLICT statement references a non-primary key column.

• Solution: The conflict target in INSERT ON CONFLICT must reference primary key columns only.

Error: Feature not supported: UPDATE with shard keys

• Cause: Hologres does not support UPDATE operations on primary key or distribution key columns.

• Solution: Do not update primary key or distribution key columns. To change these values, you must DELETE the old row and INSERT a new one.

Error: time before 1925 not supported

• Cause: Time-related functions support the range from 1925 to 2282 by default. Values outside this range cause an error.

• Solution: For functions such as to_char, to_date, or to_timestamp, set the hg_experimental_functions_use_pg_implementation GUC parameter before the SQL statement to enable a wider time range. For details, see Date and time functions.

Error: Group by key is type of imprecise not supported

• Cause: The GROUP BY clause references a column with an imprecise data type such as FLOAT.

• Solution: Use precise data types instead of FLOAT in the GROUP BY clause.

Error: CREATE TABLE is not supported for current instance

• Cause: The current instance is a Shared Cluster instance (endpoint starts with hgmc, for example hgmc-cn-xxwwwkkk), or a secondary instance. You can view the endpoint in the Hologres console. Shared Cluster instances support only external tables, not internal tables. Secondary instances are read-only.

• Solution:

  • If you use a Shared Cluster instance, create an external table using the foreign table syntax. For details, see CREATE FOREIGN TABLE. Alternatively, use an exclusive instance to create internal tables.

  • If you use a secondary instance, you must create tables on the primary instance.

Error: ALTER TABLE ALTER COLUMN SET TYPE is not supported now

• Cause: Hologres does not support changing a column data type with ALTER TABLE.

• Solution: To change a column type, recreate the table with the desired column type.

Error: ERROR: Currently materialized view does not support aggregate on expressions, only support single column

• Cause: Single-table materialized views do not support aggregate expressions on computed columns.

• Solution: Avoid using expressions in single-table materialized views. Use single-column aggregates only. For details, see Manage materialized views by using SQL.

Error: ERROR: SELECT rule's target list has too few entries

• Cause: Real-time materialized views do not support aggregate expressions on computed columns.

• Solution: Avoid using expressions in real-time materialized views. For details, see Manage materialized views by using SQL.

Error: TransactionRowStore::DumpFlushDelta Not implemented

• Cause: The current Hologres version has an implementation limitation on the DELETE FROM table USING syntax, which causes this error.

• Solution: Rewrite the SQL statement to avoid using the USING clause for batch deletes. Use the following syntax instead:

  DELETE FROM table WHERE (col1, col2) IN ((val1, val2), ...);

Error: column xxx does not exist

• Cause: The specified column does not exist in the table.

• Solution: Verify that the column name in the SQL statement is correct.

Error: Table group xxx does not exist

• Cause: The specified table group does not exist.

• Solution: Specify an existing table group when creating the table, or create the table group first.

Error: The specified partition does not exist

• Cause: This error typically occurs during data writes or updates when the target child partition table does not exist.

• Solution: Create the required child partition before writing data.

Error: create binlog reader failed: Fail to find any shards, please check if the table group is in current virtual warehouse

• Cause: The specified table group is not loaded on the current virtual warehouse.

• Solution: Verify that the table group is loaded on the virtual warehouse. If not, configure the virtual warehouse to load it. For details, see Authorize a virtual warehouse to access data.

Error: Total memory used by all existing queries exceeded memory limitation

• Cause: The query exceeded the memory limit and triggered an out-of-memory (OOM) error.

• Solution: For details, see Troubleshoot OOM errors.

Error: internal error: Datasets has different schema

• Cause: The schemas of the datasets being combined (for example, in a UNION or INSERT...SELECT statement) do not match in column count, order, or data type.

• Solution: Ensure that the column count, order, and data types are consistent across all parts of the statement.

Error: value too long for type character varying(xxx)

• Cause: The data value exceeds the character varying(n) length limit defined for the column.

• Solution: Truncate or transform the data, increase the VARCHAR length limit, or change the column type to TEXT.

Error: Exceeds the partition limitation of 512, current match xxx partitions.

• Cause: The query scans more than 512 partitions in the MaxCompute external table, which exceeds the default limit.

• Solution: Add more partition filter conditions, or increase the partition scan limit by using the following GUC parameter. For details, see FAQ about MaxCompute integration.

  set hg_foreign_table_max_partition_limit =xx;

Error: Build desc failed: Exceeds the scan limitation of 200 GB, current scan xxx GB.

• Cause: The amount of data scanned in the MaxCompute external table exceeds the 200 GB limit.

• Solution:

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

  • Import the data into a Hologres internal table, which does not have a scan size limit.

Error: syntax error at or near "xxxxx"

• Cause: The SQL statement has a syntax error.

• Solution: Review and correct the SQL syntax.

Error: DISTINCT is not implemented for window functions

• Cause: Hologres does not currently support the DISTINCT keyword in window functions.

• Solution: Remove the DISTINCT keyword from the window function.

Error: ERROR:function xxx does not exist

• Cause: The required extension is not installed, or the function call uses invalid syntax.

• Solution: Install the required extension or correct the function syntax.

Error: ERROR: function jsonb_set(json, text[], jsonb, boolean) does not exist

• Cause: The argument types do not match the JSONB_SET function signature.

• Solution: Ensure the first argument of JSONB_SET is of the JSONB type, not JSON. For details, see JSON and JSONB data types.

Error: You have NO privilege 'odps:Select' on xxx

• Cause: The current account does not have the permissions to query the MaxCompute table.

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

Error: The sensitive label of column 'xxx' is 2, but your effective label is 0

• Cause: The current account has access to only some columns in the MaxCompute table.

• Solution: Grant broader column-level permissions in MaxCompute, or query only the columns you have access to. For details about authorization, see Manage user permissions by using commands.

Error: extension "xxxxx" already exists

• Cause: The specified extension already exists in the database.

• Solution: The extension is already installed. No action is needed. To avoid this error, use CREATE EXTENSION IF NOT EXISTS.

Error: invalid input syntax for type numeric: \"\"

• Cause: The NUMERIC column contains invalid data, such as empty strings, that cannot be parsed as a numeric value.

• Solution: Clean the source data to ensure it conforms to the NUMERIC type. For example, convert empty strings to NULL or 0.

Error: invalid input syntax for integer: xxx

• Cause: The INT column contains data that cannot be parsed as an integer.

• Solution: Clean the invalid data.

Error: missing data for column "xxx". failed to query next

• Cause: The data file format is incorrect. This often occurs when the data contains the delimiter specified in the COPY command (such as a comma or space), resulting in a column count mismatch.

• Solution: Clean the data or use a different delimiter that does not appear in the data values.

Error: ERROR: INSERT has more expressions than target columns

• Cause: The number of values in the INSERT statement exceeds the number of columns in the target table.

• Solution: Ensure that the number of values in the INSERT statement matches the number of target columns.

Error: bigint out of range

• Cause: The value exceeds the valid range of the BIGINT type.

• Solution: Ensure the data is within the BIGINT range (-9223372036854775808 to 9223372036854775807), or use the NUMERIC type for larger values.

Error: numeric field overflow in function round

• Cause: The NUMERIC value exceeds its defined precision limit.

• Solution: Adjust the precision of the NUMERIC column to accommodate the data, or clean the data to fit the defined precision.

Error: InsertOverwrite insert select table data failed : column a.unsign_type does not exist

• Cause: The specified column does not exist in the table, or a parameter name is incorrect.

• Solution: Review and correct the SQL syntax.

Error: mismatched properties: table orientation is "column" but storage format is "sst"

• Cause: Mismatched table properties. Column-oriented storage was specified but the storage format was set to SST, which is incompatible.

• Solution: Review and correct the SQL statement.

Error: invalid value "" for "yyyy". Value must be an integer.

• Cause: The data does not conform to the expected datetime format.

• Solution: Ensure the data contains valid datetime values before writing.

Error: invalid byte sequence for encoding "UTF8": 0xe9 0x80

Error: relation "xxxx" already exists

• Cause: The specified table already exists in the database.

• Solution: Use a different table name, or use CREATE TABLE IF NOT EXISTS to avoid the error.

Error: character with byte sequence 0xe4 0x9e 0xab in encoding "UTF8" has no equivalent in encoding "GBK"

• Cause: A UTF-8 character has no equivalent in the target GBK encoding.

• Solution: Clean the invalid data.

Error: column "xxx" must appear in the GROUP BY clause or be used in an aggregate function

• Cause: A non-aggregated column in the SELECT list is not included in the GROUP BY clause. All columns in the SELECT list must either appear in GROUP BY or be used in an aggregate function.

• Solution: Add the column to the GROUP BY clause or use it in an aggregate function.

Error: SET_TABLE_PROPERTY and CREATE TABLE statement are not in the same transaction for table

• Cause: The CREATE TABLE and call set_table_property statements are not in the same transaction.

• Solution: Wrap the CREATE TABLE and all call set_table_property statements in the same transaction block. Example:

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

Error: column reference "xxx" is ambiguous

• Cause: The column name in a JOIN query is ambiguous because it exists in multiple tables. For example, in SELECT id FROM t1 INNER JOIN t2 ON t1.id=t2.id, id in the SELECT clause is ambiguous because it could refer to either t1 or t2.

• Solution: Qualify the column with the table name or alias (for example, t1.id).

Error: column "xxx" specified more than once

• Cause: A column name appears more than once in a CREATE TABLE statement.

• Solution: Remove the duplicate column from the CREATE TABLE statement.

Error: invalid definition of a numeric type

• Cause: A NUMERIC or DECIMAL column was defined without specifying its precision.

• Solution: Specify the precision for the NUMERIC or DECIMAL column, such as NUMERIC(38, 18).

Error: cannot cast type date to integer

• Cause: The DATE type cannot be cast to the INTEGER type.

• Solution: Modify the SQL syntax to use a valid type cast.

Error: schema "xxxx" does not exist

• Cause: The schema does not exist in the database.

• Solution: Verify that the schema name in the SQL statement is correct, or create the schema first.

Error: internal error: Connect timeout, err: std_exception: Connection refused

• Cause: The Hologres instance is unavailable or not responding.

• Solution: Submit a support ticket to investigate the Hologres instance outage.

If a column name starts with a digit, you must enclose it in double quotation marks in your query, as shown in the following example:

select bizdate,"1_day_active_users","7_day_active_users" from t_active_users;