On Oct 10, 2014, at 1:52 PM, Leonardo M. Ramé <l.r...@griensu.com> wrote:
> > El 10/10/14 a las 14:50, vibhor.ku...@enterprisedb.com escibió: >> >> On Oct 10, 2014, at 1:27 PM, Leonardo M. Ramé <l.r...@griensu.com> 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. >> >> You can do something like given below: >> CREATE TABLE temp_generate_inserts AS SELECT * FROM table id=xx >> Then use pg_dump --column-inserts -t temp_generate_inserts db1|psql db2 >> and later you can drop temp_generate_inserts table. >> >> With this you can also explore dblink_build_sql_insert function which comes >> with dblink module: >> http://www.postgresql.org/docs/9.3/interactive/contrib-dblink-build-sql-insert.html >> > > Nice!, I didn't know the create table...as select... command. Still I think optimal way of doing this will be to use COPY command something like given below: psql -c “COPY (SELECT * FROM table WHERE id=xxx) TO STDOUT” -d db1|psql -c “COPY tablename FROM STDIN” -d db2 with this, you can also explore postgresql_fdw if that helps. Thanks & Regards, Vibhor Kumar (EDB) EnterpriseDB Corporation The Postgres Database Company Blog:http://vibhork.blogspot.com
