Create INSERT INTO from SELECT in PostgreSQL

Question

How to create INSERT INTO statements, using SELECT statement? I need it because I want to delete 10k of records from a table, but if someday some of that record need to be restored I want to do that easily. I don't want to backup the whole table, because I just need to restore some of deleted rows.

Answer 1

You can use pg_dump with the --inserts option. You should also check the manual of your SQL client. Some can export data as INSERT statements.

Another option is to just copy the rows that you delete into a new table:

create table _backup
as
with deleted as (
  delete from the_table 
  where .... --<< put your condition here
  returning *
)
select  *
from deleted;

The above will delete rows from the_table and all rows that are deleted are copied into the table _backup

Answer 2

If you really needs the insert statement using a query:

   select 'INSERT INTO table values ('||bla_integer||', '''||bla_string||''')'
   from thetable; 

   Where bla_string and bla_integer are example columns from thetable...