From: | "vibhor(dot)kumar(at)enterprisedb(dot)com" <vibhor(dot)kumar(at)enterprisedb(dot)com> |
---|---|
To: | "Leonardo M(dot) Ramé" <l(dot)rame(at)griensu(dot)com> |
Cc: | PostgreSql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: psql generate insert command based on select |
Date: | 2014-10-10 18:03:54 |
Message-ID: | 42352ADE-4AC8-487D-AEAD-29999C25DC89@enterprisedb.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Oct 10, 2014, at 1:52 PM, Leonardo M. Ramé <l(dot)rame(at)griensu(dot)com> wrote:
>
> El 10/10/14 a las 14:50, vibhor(dot)kumar(at)enterprisedb(dot)com escibió:
>>
>> On Oct 10, 2014, at 1:27 PM, Leonardo M. Ramé <l(dot)rame(at)griensu(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Jim Nasby | 2014-10-10 20:29:55 | Re: Converting char to varchar automatically |
Previous Message | Adrian Klaver | 2014-10-10 18:01:33 | Re: FK check implementation |