insert overwrite table in Redshift

Question

I want to fully overwrite a Redshift table.

In e.g. Hive, I could do the following:

INSERT OVERWRITE INTO target 
SELECT s.* FROM staging s LEFT JOIN target t
ON s.primaryKey = t.primaryKey AND s.distKey = t.distKey
WHERE t.primaryKey IS NULL;

I don't get this to work in Redshift (other than dropping and re-creating the table).

Does anybody know what the Redshift syntax is for overwrite?

last time I checked postgreql didn't have an overwrite clause of INSERT — e4c5
– e4c5, Commented Jun 29, 2016 at 12:08

John Rotenstein · Accepted Answer · 2016-07-02 06:53:21Z

5

While there is no command equivalent to INSERT OVERWRITE, you can do this via:

TRUNCATE <table>
Use SELECT INTO

eg: SELECT <column> INTO <table> FROM <etc>

The TRUNCATE command is a very efficient way to delete all contents of a table. However, it cannot be reversed by a ROLLBACK, so make sure you really want to do it!

See:

answered Jul 2, 2016 at 6:53

John Rotenstein

273k28 gold badges457 silver badges543 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Peter Krauss Over a year ago

Modern pg v9.5+ can use ON CONFLICT rules (example)

Collectives™ on Stack Overflow

insert overwrite table in Redshift

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related