Copying table subset from one database to another with same schema

Question

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

Answer 1

It's not very clear what your goal is. If you want to copy data and ensure its' referential integrity, then you'll need to modify your SELECT so it picks up all dependent rows (check recursive CTE for details).

If want to just copy data , and don't care about referential integrity, you can either drop or disable FK constraints during import. Since FK in Postgres enforced with triggers, you can ALTER table disable trigger all , import, and enable them . Another way to disable all triggers would be SET session_replication_role = replica;, import data, then reset to default SET session_replication_role = DEFAULT;