If you need to move data rows from one table to another table, you’d probably immediately consider an INSERT followed by a DELETE.
But we can accomplish a data move in YSQL via a single SQL statement! This is thanks to the RETURNING clause.
Example:
yugabyte=> CREATE TABLE names (name VARCHAR);
CREATE TABLE
yugabyte=> INSERT INTO names (name) VALUES ('Jim'), ('Alex'), ('Bryan'), ('Mike'), ('Andrius') RETURNING name;
names
-----------
Jim
Alex
Bryan
Mike
Andrius
(5 rows)
INSERT 0 5
yugabyte=> SELECT * FROM names;
name
---------
Mike
Andrius
Jim
Alex
Bryan
(5 rows)
yugabyte=> CREATE TABLE names2 (name VARCHAR);
CREATE TABLE
yugabyte=> WITH move_these_name AS (DELETE FROM names WHERE name IN ('Jim', 'Alex') RETURNING name)
yugabyte-> INSERT INTO names2 SELECT name FROM move_these_name;
INSERT 0 2
yugabyte=> SELECT name FROM names;
name
---------
Mike
Andrius
Bryan
(3 rows)
yugabyte=> SELECT name FROM names2;
name
------
Jim
Alex
(2 rows)
Have Fun!