psql generate insert command based on select

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

Thanks & Regards,
Vibhor Kumar
(EDB) EnterpriseDB Corporation
The Postgres Database Company
Blog:http://vibhork.blogspot.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

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