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

  • If you don't want the foreign key to be enforced, then drop it (or describe what you do want to happen). ON CONFLICT has nothing to do with this, it is for unique constraints, not foreign key constraints.
    – jjanes
    May 13, 2019 at 17:47
  • I suggest you to use postgres_fdw.
    – Sahap Asci
    May 14, 2019 at 11:44

1 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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.