2

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
2

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:

CREATE TABLE dpd.inactive
(
    id          BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,
    drug_code   INTEGER NOT NULL REFERENCES dpd.qrym_drug_product,
    ...

I had to fix it up to be this:

CREATE TABLE dpd.inactive
(
    id          BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,
    drug_code   INTEGER NOT NULL REFERENCES dpd.qrym_drug_product (drug_code),
    ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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