Greenplum Streamig Server(GPSS) reports "upsert is not supported for schema01.table01 because table storage is ao."

Products

• VMware Tanzu Data Intelligence

Issue/Introduction

The message "upsert is not supported for schema01.table01 because table storage is ao" is reported in GPSS logs when running a job with "MODE: MERGE" and the table in the database is an AppendOptimized(AO) table.

Cause

Information on UPSERT can be found in the documentation in "ON CONFLICT Clause". The UPSERT is an INSERT like

INSERT INTO table01 
   SELECT * FROM external_table01
   ON CONFLICT DO UPDATE ....

This allows the new data to be inserted and exiting data to be updated in the one statement instead of doing 2 statements (INSERT and then UPDATE). 

UPSERT can be supported only if the following conditions are met:

1. GPDB version >= 7 (Postgres version >= 12.12)
2. Table storage type is heap
3. Table contains a primary key or unique key with the "MATCH COLUMNS"

Resolution

The message is just informational. The job will fall back to the normal method of individual INSERT and UPDATE statements.