/* PostgreSQL CTAS */ :::1. Create a copy of the table create | SergeyGurin

/*
PostgreSQL
CTAS
*/

:::1. Create a copy of the table
create table replication.item_data2
(
like replication.item_data INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES
);

:::2. Insert the required data into the new table
insert into replication.item_data2
select *
from replication.item_data
where system_date > current_date - 90 * INTERVAL '1 DAY';

:::3. Rename original and new tables
alter table replication.item_data rename to item_data_for_drop;
alter table replication.item_data2 rename to item_data;

:::4. Delete old table
drop table replication.item_data_for_drop;