How do I copy only 2 tables in Postgres and ignore foreign keys and constraints?

Question

I need to copy 2 tables from production to development. They are the same except the production table has a bit more data in it. I tried pg_dump to dump from production and copy to development, but it complained about foreign key constraints. How do I turn off constraints temporarily? I don't want to copy the whole database because it's too much data, and I want to keep my test data and users.

ALTER TABLE
DROP INDEX
ERROR:  cannot drop constraint foods_pkey on table foods because other objects depend on it
DETAIL:  constraint fk_rails_7e399284de on table histories depends on index foods_pkey
        constraint fk_rails_8d89280489 on table food_nutrients depends on index foods_pkey

Here are the two tables:

                                        Table "public.foods"
      Column      |            Type             |                     Modifiers
------------------+-----------------------------+----------------------------------------------------
 id               | integer                     | not null default nextval('foods_id_seq'::regclass)
 ...
Indexes:
    "foods_pkey" PRIMARY KEY, btree (id)
    "index_foods_on_food_category_id" btree (food_category_id)
Foreign-key constraints:
    "fk_rails_a28abb337f" FOREIGN KEY (food_category_id) REFERENCES food_categories(id)
Referenced by:
    TABLE "histories" CONSTRAINT "fk_rails_7e399284de" FOREIGN KEY (food_id) REFERENCES foods(id)
    TABLE "food_nutrients" CONSTRAINT "fk_rails_8d89280489" FOREIGN KEY (food_id) REFERENCES foods(id)

And

                                     Table "public.food_categories"
   Column   |            Type             |                          Modifiers
------------+-----------------------------+--------------------------------------------------------------
 id         | integer                     | not null default nextval('food_categories_id_seq'::regclass)
 ...
Indexes:
    "food_categories_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "foods" CONSTRAINT "fk_rails_a28abb337f" FOREIGN KEY (food_category_id) REFERENCES food_categories(id)

I also tried --disable-triggers but it didn't help.

you want get only data from `food` and `food_categories` ?, how do you need it, like create new table in development db and copy data from production db to it or what ? — Vivek S., Sep 25 '15 at 04:37
Have you seen this - http://stackoverflow.com/questions/3195125/copy-a-table-from-one-database-to-another-in-postgres ? — Vivek S., Sep 25 '15 at 04:38
If it is a regular operation (Copy from prod to dev), you can declare your constraint (FK) as DEFERRABLE, and can be checked at the end of the `copy` transaction. Please look at this exellent [answer](http://stackoverflow.com/a/2681413/1216680) — Houari, Sep 25 '15 at 09:01
@Houari But don't I have to create the constraint with DEFERRABLE? What if the constraint and index already exists? How do I drop it and the table so I can re-create it? — Chloe, Sep 25 '15 at 17:35

score 0 · Answer 1 · answered Sep 25 '15 at 05:42

0

You can first copy the "food_categories" table. After that you can copy the "foods" table.

answered Sep 25 '15 at 05:42

Akhilesh

1,243
4
16
49

But I can't even drop the index or the table first. It doesn't matter which order I try to copy in because I have to delete them first, and it won't let me, as you can see in the error in the question. – Chloe Sep 25 '15 at 17:34
@Chloe You have to drop the "histories" table and "food_nutrients" first.Because they depend on the "foods" table. You can also try "DROP TABLE name CASCADE " – Akhilesh Sep 28 '15 at 07:24
That's the thing. I don't want to remove my local test data. I only want to add data and temporarily turn off constraints to add these missing rows in these tables. – Chloe Sep 28 '15 at 19:32
@Chloe You can take a back up of those tables using "pgAdmin" and later import them back. – Akhilesh Sep 29 '15 at 10:16

How do I copy only 2 tables in Postgres and ignore foreign keys and constraints?

1 Answers1