CODEWITHSUNDEEP
sqlpostgresql
iskandarblue
iskandarblue

Reputation: 7526

Truncating part of a postgres table

I am running Postgres version 10.01

psql -V
psql (PostgreSQL) 10.5

I have a table mytable with about 250 million rows - my objective is to create a new table newtable and copy about half of the mytable into newtable (SELECT * WHERE time > '2019-01-01 ), then to delete the records I copied from mytable

Of course, I want to keep all indices in mytable

What is the most efficient command to do this in psql ? TRUNCATE TABLE is efficient but will remove all rows. DELETE would probably take a lot of time and prevent inserts from happening (INSERTS are scheduled every 10 mins)

Any suggestions would be helpful

Upvotes: 0

Views: 2243

Answers (2)

GMB
GMB

Reputation: 222482

You would need to proceed in two steps.

First, copy the rows to the new table. You can use a CREATE..AS SELECT statement (but you will need to recreate indexes and other objects such as constraints manually on the new table after that).

CREATE TABLE  new_table
AS SELECT * FROM old_table WHERE time > '2019-01-01

Then, delete records from the old table. It looks like an efficient way would be to JOIN with the new table, using the DELETE...USING syntax. Assuming that you have a primary key called id :

DELETE FROM old_table o
USING new_table n
WHERE n.id = o.id

(Be sure to create an indice on id in the new table before running this).

Upvotes: 2

jkramer
jkramer

Reputation: 91

If you are just trying to delete rows couldn't you just treat your delete as a transaction. Unless your insert is dependent on the existing data in the table, there should be no blocking.

Upvotes: 0

Related Questions