Upserting in GreenPlum

1
votes

How can I upsert a record in GreenPlum while copying the data from a CSV file. The CSV file has multiple records for a given value of the primary key. If a row with some value already exists in the database I want to update that record. Otherwise, it should append a new row.

1
greenplum
Short answer is delete and then insert. I wrote a blog post a long time ago on how to do it: pivotalguru.com/?p=104 - Jon Roberts

1 Answers

0
votes

One way to do this is to copy the data to a staging table, then insert/update from that table.

Here is an example of that:

-- Duplicate the definition of your table.
CREATE TEMP TABLE my_table_stage (LIKE my_table INCLUDING DEFAULTS);


-- Your COPY statment
COPY my_table FROM 'my_file.csv' ...


-- Insert any "new" records
INSERT INTO my_table (key_field, data_field1, data_field2)
SELECT
    stg.key_field,
    stg.data_field1,
    stg.data_field2
FROM
    my_table_stage stg
WHERE
    NOT EXISTS (SELECT 1 FROM my_table WHERE key_field = stg.key_field);


-- Update any existing records
UPDATE my_table orig
SET
    data_field1 = stg.data_field1,
    data_field2 = stg.data_field2
FROM
    my_table_stage stg
WHERE
    orig.key_field = stg.keyfield;