[SOLVED] move part of table postgresql to another database

move part of table postgresql to another database

I have a mission and searching for advice. On my postgresql database one of tables contains data from few years - I want to move part of it to another database. Condition of splitting will be record create date, but this filed is varchar. This table is quite big, what is your recommendation to do it fast and safe?

Solution

I'd use the \copy command. The copy is described here https://www.postgresql.org/docs/current/sql-copy.html. The \copy is available inside psql. The difference is that copy assumes the path to a file is on the database machine, while\copy assumes it's on the machine where the psql runs.

You can use \copy like this (this is an example from the above page):

\COPY (SELECT * FROM country WHERE country_name LIKE 'A%') 
TO 'country_data';

This way you have all the rows written to this file. Then you can load it to another database also using copy:

\COPY country FROM 'country_data';

Then you can delete the data with:

DELETE FROM country WHERE country_name LIKE 'A%';

You could also make copy and delete in one query. However, in this case I'd rather carefully check if the data in the new database is fine before deleting it.