I'm trying to batch-copy data in a specific order from one table to another in PostgreSQL 12-beta2. The table is not using sequences, but contains an composite unique Primary Key (user_id, object_id
).
In order to determine where to start for the next batch, I'd like to start off from the last inserted row (WHERE user_id >= last_user_id AND object_id > last_object_id
).
Starting off with this:
INSERT INTO dest_table
SELECT (user_id, object_id, object_type, colN, ...)
FROM source_table
ORDER BY user_id, colN, object_id -- this is indexed
LIMIT 1000 -- batch size
RETURNING user_id, object_id;
... returns a table of 1000 tuples. I'd like to obtain the last inserted tuple from it.
I've tried to do a SELECT around it, like this:
SELECT user_id, object_id FROM (
INSERT INTO dest_table
SELECT (user_id, object_id, object_type, colN, ...)
FROM source_table
ORDER BY user_id, colN, object_id -- this is indexed
LIMIT 1000 -- batch size
RETURNING user_id, object_id
)
ORDER BY user_id DESC, colN DESC, object_id DESC
LIMIT 1
RETURNING user_id, object_id;
But that returns a syntax error:
ERROR: syntax error at or near "INTO"
LINE 2: INSERT INTO dest_table
^
I've also attempted RETURNING ... INTO variable
as described here, but that fails too:
ERROR: syntax error at or near "INTO"
LINE 23: RETURNING user_id, object_id INTO my_variable;
^
Do I need to create a function for this (e.g. plpgsql) or am I missing something obvious in plain SQL that let me do this? That would be highly favorable.