Re: Synchronizing a table that is two different databases : Need to dump a table a insert from db1 and change the insert statements into UPDATE statements - Mailing list pgsql-general

Is there data in db2 that is not in db1, and ought not to be?  If not,
then I suggest dumping the table from db1, loading it into a different
schema in db2, then moving the table in db2 out of the way and the
new-table into place, all in one transaction.

If you have data in db2 that is _not_ in db1 but that should be there,
then the easier way is likely again to load up the table from db1 into
a different schema, then perform updates and inserts as needed on the
db2 table you're trying to modify.

Finally, and I suppose this is obvious, if there are a lot of records
involved you may want to do this in hunks so that you can perform the
necessary vacuums and so on on the target table, or else have a very
long-running transaction.

A

On Thu, Mar 27, 2014 at 12:16:32PM +0200, Khangelani Gama wrote:
> Hi all
>
>
>
> Synchronizing a *table* that is in two different databases(e.g *db1 and db2*).
>
>
>
>
> Please help me with this. I need to dump a table as INSERTS from db1
> (postgres 9.1.2) and change the INSERT statements into UPDATE statements in
> order to apply that change in *db2(postgres 8.3.0.112)* which has the same
> *table* as *db1*. Where the record does not exist I need to insert that
> record.   There is more than 1000 INSERTS I need to convert to UPDATES for
> the same table. Please help .
>
>
>
> *Example:*
>
>
>
> Below it's the INSERT from postgres 9.1.2 DATABASE which should convert to
> UPDATE statement.
>
>
>
> INSERT INTO* table* (br_cde, br_desc, br_active, br_closed, grp_cde,
> ctry_cde, state_cde, br_addr_line1, br_addr_line2, br_addr_line3,
> br_addr_line4, br_addr_cde, br_post_addr_line1, br_post_addr_line2,
> br_post_addr_line3, br_post_addr_line4, br_post_addr_cde, br_phone,
> tax_cde, br_server_name, br_lang_ind, bureau_cde, br_risk_typ_cde,
> br_access_ccd, br_access_bureau, br_network_active, br_desc_short,
> br_is_trading, br_is_test, br_is_nomodem, br_is_whs, br_is_merch,
> br_is_cco, br_is_central, br_is_merch_active, br_central_brn, br_merch_brn,
> br_cco_brn, br_cgo_brn, br_cluster_brn, br_contact, br_tollfree, br_fax,
> br_telex, br_email, ops_cde, ops_director, br_cmpy_reg, br_tax_reg,
> audit_id, comp_cde, br_batch, br_release_pilot_type, br_is_order_active,
> bnk_id, br_class_cde, br_erp_xref, br_to_open, br_to_close, br_comments,
> br_is_hire, br_is_voucher, br_is_clear_center, br_is_headoffice,
> br_is_nsp_active, usr_pass_history, br_network_protocol,
> br_gen_ins_central_brn, br_cpi_ins_central_brn, br_rsc_area, br_rsc_perc,
> br_switch_deduc, br_tvlic_dealer_no, br_cco_followup, br_triad_live,
> br_do_cust_locking, br_is_rainbow, br_nca_live, br_ncr_reg_no, prov_cde,
> br_is_data_archive, br_icu_brn, br_living_expenses_live, br_ab_dorder_live,
> br_ab_receipts_active, br_spiv_max_alloc_perc, br_merger_brn,
> br_dep_bnk_acc, br_merchant_number, br_goods_ins_live, br_cgs_connection,
> whs_cde, br_crm_brn, subscription_active, br_prn_doc_default_active,
> br_use_jms, br_sso_active, br_paym8_properties, creditors_supp_no_prefix,
> br_block_debtors_obo, ccms_branch, br_is_ccms_live, ceres_conv_flag,
> cims_branch, br_is_cims_live, br_accept_sql_releases) VALUES ('9940',
> 'ELLERINES CENTRAL - 9940', true, NULL, '1', 'BAF', 'BAF', '209 SMIT
> STREET', 'BRA', 'Jgf', 'Jrg', '3025', '209 SMIT STREET', 'BRA', 'Jrg',
> NULL, '2017', '(4562) 712 1300', '1', 'dfgike.com', 'ENG', 'ITC', 'L',
> false, false, false, 'BATCH - 9940', false, false, false, false, false,
> false, true, false, '9940', NULL, NULL, '10.52.1.31', '9940', 'DOUG', NULL,
> '(4562) 712 1300' ', NULL, NULL, '001', NULL, '1969/02687/07', NULL, 0,
> NULL, '9940', NULL, false, NULL, NULL, NULL, NULL, NULL, NULL, false,
> false, false, false, false, 30, 'WS2~WS2', '1002', '1002', NULL, NULL,
> false, NULL, NULL, false, true, false, true, 'NC', NULL, true, NULL, true,
> true, NULL, NULL, NULL, NULL, NULL, true, 'oracle_live', NULL, NULL, true,
> NULL, true, false, NULL, NULL, NULL, NULL, NULL, NULL, NULL, false, false);
>
>
>
> CONFIDENTIALITY NOTICE
> The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential
> information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by
anyone
> other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer
immediately
> and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no
liability
> for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.

--
Andrew Sullivan
ajs@anvilwalrusden.com