Append and Overwrite in Amazon Redshift

Question

As Redshift is based on PostgreSQL, does it have an option to overwrite or append data in table while copying from S3 to redshift?

Only thing I got is use of triggers but they don't accept any argument.

All I need to write a script which takes an argument as yes/no (or similar) if the data is already in the table.

Answer 1

When loading data from Amazon S3 into Amazon Redshift using the COPY command, data is appended to the target table.

Redshift does not have an "overwrite" option. If you wish to replace existing data with the data being loaded, you could:

• Load the data into a temporary table

• Delete rows in the main table that match the incoming data, eg:

  DELETE FROM main-table WHERE id IN (SELECT id from temp-table)

• Copy the rows from the temporary table to the main table, eg:

  SELECT * FROM temp-table INTO main-table

See: Updating and Inserting New Data

Answer 2

Redshift doesn't allow you to create triggers or events like other sql databases, the solution I found is to run update (sql query)though you can use also Python or other language and schedule the Rscript with crontab task.