CODEWITHSUNDEEP
sqlpostgresqlcsv
alexanoid
alexanoid

Reputation: 25862

Import ONE_2_MANY data from flat CSV file into two PostgreSQL tables

I have a following PostgreSQL schema:

CREATE SEQUENCE user_question_rev_user_question_rev_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;         

CREATE TABLE user_question_rev (
  user_question_rev_id integer NOT NULL DEFAULT nextval('user_question_rev_user_question_rev_id_seq'::regclass),
  email_address character varying(100),
  entry_id integer NOT NULL,
  create_date timestamp without time zone,
  CONSTRAINT user_question_rev_pkey PRIMARY KEY (user_question_rev_id)
);

CREATE SEQUENCE user_question_user_question_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1; 

CREATE TABLE user_question (
  user_question_id integer NOT NULL DEFAULT nextval('user_question_user_question_id_seq'::regclass),
  user_question_rev_id integer NOT NULL,
  question text NULL,
  answer text NULL,
  create_date timestamp without time zone,
  CONSTRAINT user_question_pkey PRIMARY KEY (user_question_id),              
  CONSTRAINT user_question_user_question_rev_id_fkey FOREIGN KEY (user_question_rev_id)
    REFERENCES user_question_rev (user_question_rev_id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION
);

Also, I have a CSV file with a following structure:

Email Address        |   Question 1  |  Question 2   |  Question 3   |  Entry Id  |  Entry Date
--------------------------------------------------------------------------------------------------------
[email protected]    |   Answer 1_1  |  Answer 2_1   |  Answer 3_1   |  667       |  2016-12-02 06:15:13
[email protected]    |   Answer 1_2  |  Answer 2_2   |  Answer 3_2   |  666       |  2016-12-02 05:15:59
[email protected]    |   Answer 1_3  |  Answer 2_3   |  Answer 3_3   |  665       |  2016-12-01 05:20:22
[email protected]    |   Answer 1_4  |  Answer 2_4   |  Answer 3_4   |  662       |  2016-11-29 15:16:58
[email protected]    |   Answer 1_5  |  Answer 2_5   |  Answer 3_5   |  651       |  2016-11-28 16:14:52
[email protected]    |   Answer 1_22 |  Answer 2_22  |  Answer 3_22  |  681       |  2016-12-03 02:11:01

I need to load data from this CSV file into PostgreSQL schema present above.

For every record in CSV I need to create an appropriate new record in user_question_rev table and new records(ONE_2_MANY) in user_question table and place there:

(CSV) Email Address -> (user_question_rev) email_address
(CSV) Entry Id      -> (user_question_rev) entry_id
(CSV) Entry Date    -> (user_question_rev) create_date

and appropriate ONE_2_MANY by user_question.user_question_rev_id:

user_question_rev.user_question_rev_id -> user_question.user_question_rev_id
(CSV) Question 1                       -> (user_question) question 
(CSV) Entry Date                       -> (user_question) create_date
(CSV) Question 2                       -> (user_question) question 
(CSV) Entry Date                       -> (user_question) create_date
(CSV) Question 3                       -> (user_question) question 
(CSV) Entry Date                       -> (user_question) create_date

Please show how it can be done with PostgreSQL and SQL.

Upvotes: 0

Views: 49

Answers (1)

JosMac
JosMac

Reputation: 2322

  • Create import table with structure based on csv - something like follows - I recommend all columns of type text this way you can import without errors. CSV files sometimes contain very strange values... create table my_import ( Email Address text, Question1 text, Question2 text, Question3 text, EntryId text EntryDate text )
  • Import using psql + COPY command (if file is on server) or "\copy" (if file is on your local machine) - see in documentation
  • And select from this imported table the same way you described in your question. Just transform your rules into more very simple SQL statements. Simplicity is your fried in cases like this :-) of course do not forget proper casting into target types.

Upvotes: 1

Related Questions