postgresql 9.2 - Update ID column from another table while using copy from csv -

i trying import data table using copy csv file. table in want 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 deferred ) 

the primary key referencing 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 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 deferred ) 

i using copy command:

copy forms_member_registration("agrihub", "ward_number", "area","first_name",        "last_name", "other_name", "sa_id_number", "gender", "phone_number") '/opt/project/migration/file-3.csv' delimiter ',' csv header; 

giving error:

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

so problem see "baseform_ptr_id" needs retrieved id column of forms_baseformmodel table each entry id gets created when entry made forms_baseformmodel.

how can create entry in forms_baseformmodel, retrieve , add tuple being copied?

hope makes sense... kinda new me.

thanks in advance

this rather common problem. must is:

• copy data temporary or unlogged table;

• insert real_table select ... temp_table inner join other_table ...

in other words, copy staging table, generate real data set join , insert join product real table.

it's somewhat related bulk upsert problem.

so in case you'd create temp_forms_member_registration, copy csv including user_id column wish replace, then:

insert forms_member_registration(   baseformmodel_ptr_id,    "agrihub",    ... ) select    fbfm.id,    tfmr."agrihub",   ... temp_forms_member_registration tfmr inner join forms_baseformmodel on (tfmr.user_id = fbfm.user_id);