Using Postgresql 9.5 on Ubuntu 16.04. Importing large .csv files into a new database using COPY FROM
. The database is created by the same script that attempts to import the .csv files, so I know the tables are initially empty. The script successfully imports around 45,000 rows into the first table.
The problem:
Partway through importing the 2nd table's data, I get errors like this one:
ERROR: insert or update on table "inactive" violates foreign key constraint "inactive_drug_code_fkey"
DETAIL: Key (drug_code)=(47543) is not present in table "qrym_drug_product".
But when I check the 1st table, I see that that key exists:
select * from dpd.qrym_drug_product where drug_code=47543;
id | drug_code | product_categorization | class | ...
-------+-----------+------------------------+-------+----
17970 | 47543 | | Human | ...
(1 row)
Why would PostgreSQL complain about that key not being present in the table, when I can see it does exist?
I'm not using table inheritance (I saw the caveat on [1]).
Here is the first table:
\d dpd.qrym_drug_product;
Table "dpd.qrym_drug_product"
Column | Type | Modifiers
----------------------------+---------+--------------------------------------------------------------------
id | bigint | not null default nextval('dpd.qrym_drug_product_id_seq'::regclass)
drug_code | integer | not null
product_categorization | text |
class | text |
drug_identification_number | text |
ai_group_no | text |
Indexes:
"qrym_drug_product_pkey" PRIMARY KEY, btree (id)
"qrym_drug_product_drug_code_key" UNIQUE CONSTRAINT, btree (drug_code)
Referenced by:
TABLE "dpd.inactive" CONSTRAINT "inactive_drug_code_fkey" FOREIGN KEY (drug_code) REFERENCES dpd.qrym_drug_product(id) ON DELETE RESTRICT
Here is the definition for the 2nd table that bombs partway through the COPY FROM
:
\d dpd.inactive;
Table "dpd.inactive"
Column | Type | Modifiers
----------------------------+---------+-----------------------------------------------------------
id | bigint | not null default nextval('dpd.inactive_id_seq'::regclass)
drug_code | integer | not null
drug_identification_number | text |
brand_name | text |
history_date | date |
Indexes:
"inactive_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"inactive_drug_code_fkey" FOREIGN KEY (drug_code) REFERENCES dpd.qrym_drug_product(id) ON DELETE RESTRICT
Best Answer
Someone named "RhodiumToad" in the #postgresql channel on IRC pointed out the problem. Because I didn't explicitly state the column name, the 2nd table is referencing the primary key column "id" from the first table. Instead, I wanted it to reference the
drug_code
column.So where I had previously defined the 2nd table as:
I had to fix it up to be this: