Update ID column from another table while using copy from csv

Question

I am trying to import data to a table using COPY from a csv file. This is the table in which I want to import:

CREATE TABLE public.forms_member_registration
(
   baseformmodel_ptr_id integer NOT NULL,
   "Agrihub" character varying(200) NOT NULL,
   "Ward_Number" character varying(300) NOT NULL,
   "Area" character varying(300) NOT NULL,
   "First_Name" character varying(300) NOT NULL,
   "Last_Name" character varying(300) NOT NULL,
   "Other_Name" character varying(300) NOT NULL,
   -----------snip--------------------------------
   "L3_Modules_Completed" character varying(200),
   "L3_Specify_Other" character varying(300) NOT NULL,
   gps_location geometry(Point,4326),

   CONSTRAINT forms_member_registration_pkey 
      PRIMARY KEY (baseformmodel_ptr_id),
   CONSTRAINT baseformmodel_ptr_id_refs_id_c03f6c72 
      FOREIGN KEY (baseformmodel_ptr_id)
          REFERENCES public.forms_baseformmodel (id) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
)

The primary key is referencing this table:

CREATE TABLE public.forms_baseformmodel
(
    id integer NOT NULL DEFAULT nextval('forms_baseformmodel_id_seq'::regclass),
    user_id integer NOT NULL,
    created_at timestamp with time zone NOT NULL,

    CONSTRAINT forms_baseformmodel_pkey 
        PRIMARY KEY (id),
    CONSTRAINT user_id_refs_id_3a410ec9 
        FOREIGN KEY (user_id)
           REFERENCES public.auth_user (id) MATCH SIMPLE
           ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
)

I am using this copy command:

COPY forms_member_registration("Agrihub", "Ward_Number", "Area","First_Name",        "Last_Name", "Other_Name", "SA_ID_Number", "Gender", "Phone_Number") FROM '/opt/project/migration/file-3.csv' DELIMITER ',' CSV HEADER;

Giving this error:

ERROR:  null value in column "baseformmodel_ptr_id" violates not-null constraint

So the problem as I see it is that "baseform_ptr_id" needs to be retrieved from the id column of the forms_baseformmodel table for each entry but id only gets created when an entry is made to forms_baseformmodel.

How can I create the entry in forms_baseformmodel, retrieve it and add it to the tuple being copied?

Hope that makes sense... This is all kinda new for me.

Thanks in advance

score 0 · Answer 1 · edited May 23 '17 at 12:01

This is a rather common problem. What you must do is:

COPY the data to a TEMPORARY or UNLOGGED table;
INSERT INTO real_table SELECT ... FROM temp_table INNER JOIN other_table ...

In other words, copy to a staging table, then generate the real data set with a join and insert the join product into the real table.

It's somewhat related to the bulk upsert problem.

So in your case you'd create a temp_forms_member_registration, copy the csv into it including the user_id column you wish to replace, then:

INSERT INTO forms_member_registration(
  baseformmodel_ptr_id, 
  "Agrihub", 
  ...
)
SELECT 
  fbfm.id, 
  tfmr."Agrihub",
  ...
FROM temp_forms_member_registration tfmr
INNER JOIN forms_baseformmodel ON (tfmr.user_id = fbfm.user_id);

Update ID column from another table while using copy from csv

1 Answers1