[SOLVED] Move data from one table to another with Redshift

Move data from one table to another with Redshift

I wanted to move log datas that have a specific user_id to a new table on Redshift. I've started playing with WITH block like :

WITH moved_rows AS (
    DELETE FROM sensor_log_enable
    USING sensor_log_disable
    WHERE sensor_log_enable.user_id
       IN (16,17,18)
    RETURNING sensor_log_enable.*
)
INSERT INTO sensor_log_disable
SELECT * FROM moved_rows;

But redshift doesn't like it.

ERROR:  syntax error at or near "DELETE"
LINE 2:     DELETE FROM active_connections

Redshift doesn't seem to include DELETE in WITH block. What's the best strategy then ? 1 INSERT INTO then an INNER JOIN OR LEFT OUTER JOIN with DELETE?

Solution

To 'move' data between tables, you must:

Use INSERT INTO to copy the data to the target table
Use DELETE to delete the data from the source table

They must be performed as separate SQL commands. You can, however, wrap those commands in BEGIN/END statements to commit them as one transaction:

BEGIN;
INSERT INTO...;
DELETE FROM...;
END;