Re: psql generate insert command based on select

On 10/10/2014 10:42 AM, "Leonardo M. Ramé" wrote:
>
> El 10/10/14 a las 14:37, Adrian Klaver escibió:
>> On 10/10/2014 10:27 AM, "Leonardo M. Ramé" wrote:
>>> Hi, today I needed to re-create certain records deleted from a mysql
>>> database, so I restored an old backup, opened a terminal and logged in
>>> to the old database using the "mysql" command line utility, then opened
>>> a new terminal with mysql connected to the production database. Then did
>>> a "select * from table where id=xxx \G;" to display a record, then, on
>>> the other terminal I had to write "insert into table(field1,
>>> field2,...,fieldN) values(...);" for each record.
>>>
>>> While doing that I tought of a neat feature that psql could provide,
>>> that is something like "\insert for select * from table where id=xxx;"
>>> this should create the insert command for the requested query.
>>>
>>> Is such a thing already present in psql?.
>>
>> I may be missing something but:
>>
>> http://www.postgresql.org/docs/9.3/interactive/sql-insert.html
>>
>> INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';
>>
>> or are you thinking of something that takes a SELECT query and turns
>> it into a series of INSERT queries.
>>
>> The only way I can of doing this is to use pg_dump -t some_table -a
>> --inserts or --column-inserts
>
> The problem is I needed the make the insert statements in another
> database, not the one I was connected to for soing the select.

Another Postgres database or some other type.

>
> The pg_dump could help in part, because after creating it I need to
> delete all the unneeded records.

Well in Postgres 9.3+ you have the PROGRAM option to COPY that allows
you to pipe to/from an external program for further processing. In 9.2-
you could still use COPY to output to a CSV format say and then process
in a separate step. This could be done in a transaction in either case,
where the last step is a DELETE. Though whatever happens outside the
database would not be covered by the transaction.

Again if you are in 9.3+ you would be able to use the Postgres FDW to
write from one Postgres database to another. Otherwise you could use dblink:

http://www.postgresql.org/docs/9.3/interactive/dblink.html

>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com