Обсуждение: psql generate insert command based on select
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?. Regards, -- Leonardo M. Ramé Medical IT - Griensu S.A. Av. Colón 636 - Piso 8 Of. A X5000EPT -- Córdoba Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19 Cel.: +54 9 (011) 40871877
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 > > Regards, > -- Adrian Klaver adrian.klaver@aklaver.com
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. The pg_dump could help in part, because after creating it I need to delete all the unneeded records. -- Leonardo M. Ramé Medical IT - Griensu S.A. Av. Colón 636 - Piso 8 Of. A X5000EPT -- Córdoba Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19 Cel.: +54 9 (011) 40871877
On Oct 10, 2014, at 1:27 PM, Leonardo M. Ramé <l.rame@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 terminaland logged in to the old database using the "mysql" command line utility, then opened a new terminal with mysql connectedto the production database. Then did a "select * from table where id=xxx \G;" to display a record, then, on theother 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 * fromtable 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 Thanks & Regards, Vibhor Kumar (EDB) EnterpriseDB Corporation The Postgres Database Company Blog:http://vibhork.blogspot.com
El 10/10/14 a las 14:50, vibhor.kumar@enterprisedb.com escibió: > > On Oct 10, 2014, at 1:27 PM, Leonardo M. Ramé <l.rame@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 terminaland logged in to the old database using the "mysql" command line utility, then opened a new terminal with mysql connectedto the production database. Then did a "select * from table where id=xxx \G;" to display a record, then, on theother 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 * fromtable 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 > > Thanks & Regards, > Vibhor Kumar > (EDB) EnterpriseDB Corporation > The Postgres Database Company > Blog:http://vibhork.blogspot.com > Nice!, I didn't know the create table...as select... command. -- Leonardo M. Ramé Medical IT - Griensu S.A. Av. Colón 636 - Piso 8 Of. A X5000EPT -- Córdoba Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19 Cel.: +54 9 (011) 40871877
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.klaver@aklaver.com
On Oct 10, 2014, at 1:52 PM, Leonardo M. Ramé <l.rame@griensu.com> wrote:
El 10/10/14 a las 14:50, vibhor.kumar@enterprisedb.com escibió:
On Oct 10, 2014, at 1:27 PM, Leonardo M. Ramé <l.rame@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.
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
(EDB) EnterpriseDB Corporation
The Postgres Database Company
Blog:http://vibhork.blogspot.com