Truncating part of a postgres table

Question

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

Insert all your rows into the new table and then do INNER JOIN deletes — dfundako
– dfundako, Commented Jan 16, 2019 at 17:32
Unrelated, but: psql -V prints the version of the command line utility psql. It's not the version of the Postgres server. That can reliably only obtained by running select version(); — user330315
– user330315, Commented Jan 16, 2019 at 17:52
If you had created a partitioned table, it would have been very simple. — Laurenz Albe
– Laurenz Albe, Commented Jan 16, 2019 at 19:13

GMB · Accepted Answer · 2019-01-16 18:12:35Z

2

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).

answered Jan 16, 2019 at 18:12

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jkramer · Accepted Answer · 2019-01-16 17:29:46Z

0

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.

answered Jan 16, 2019 at 17:29

jkramer

911 silver badge7 bronze badges

Collectives™ on Stack Overflow

Truncating part of a postgres table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related