4

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.

6

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

6

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...
1
  • 1
    Code that writes code! Sep 15 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.