I am trying to subset of a table from one table to another
users(id, name, referrer_id, country_id) this table have self referential association with refferer_id as foreign key to itself. I tried following query
psql source_table -c 'COPY (SELECT * FROM users where country_id = 2) TO stdout' | psql target_table -c 'COPY users FROM stdin'
Issue with above approach is reffer may not be in the same subset, so it will show the error
ERROR: insert or update on table "users" violates foreign key constraint "users_referrer_id_fk" DETAIL: Key (referrer_id)=(123) is not present in table "users".
I tried to use ON CONFLICT but it seems that ON CONFLICT syntax is not available in COPY TABLE syntax. Is there a better way to transfer subset of tables between databases