3

I'm trying to import data from a csv to a postgresql database.

the db table looks like this:

CREATE TABLE texts(
text_id serial PRIMARY KEY,
topic_id integer REFERENCES topics,
format_id integer REFERENCES formats,
text text
);

Content: 1;1;1;sample text

CREATE TABLE topics(
topic_id serial PRIMARY KEY,
topic varchar(50) NOT NULL
);

Content: 1;comedy

CREATE TABLE formats(
format_id serial PRIMARY KEY,
format varchar(50) NOT NULL
);

Content: 1;A5

and an csv sheet which looks like this:

topic;format;text
comedy;A5;asdlakjsdlahsdasd
love;A6;laksdasdkhjasf

Now i want to insert the content of the csv. How can I translate the csv contents like "format" to the format_ids in the formats table ?

Any help is much appreciated.

UPDATE:

Creating a tmp table and inserting the csv is easy.

create table tmp (
    id serial PRIMARY KEY,
    topic_id integer,
    topic varchar(255),
    format_id integer,
    format varchar(255),
    text text
);

copy tmp(topic, format, text) FROM 'test.csv' DELIMITER ';' CSV HEADER;

But how do I get the right ID's now to insert them into the text table?

1 Answer 1

3

I don't see where you are referencing the content of your CSV anywhere in what you posted.. (at least before any future edits). You should be using the COPY command.

http://www.postgresql.org/docs/current/static/sql-copy.html

EDIT

Since Topic and Format tables appear to be lookup tables for referential integrity, the correct course of action would be to insert the values into those tables FIRST .. and then insert into the other table using a SELECT.. Answer below:

INSERT INTO topics (topic) SELECT DISTINCT topic FROM tmp;
INSERT INTO formats (format) SELECT DISTINCT format FROM tmp;
INSERT INTO texts (topic_id, format_id, text)
  SELECT b.topic_id, c.format_id, a.text
  FROM tmp a
  JOIN topics b ON a.topic = b.topic
  JOIN formats c ON a.format = c.format;

Don't forget to clean up when you're done.

DROP TABLE tmp;
5
  • I've edited my post, I hope its clearer now.
    – AnC
    Dec 17, 2014 at 9:47
  • ok ... but my comment still holds true - you are not using the COPY command anywhere. You need to use the COPY command to load the data from the CSV (which exists OUTSIDE the db) INTO the db.. Load the whole CSV raw into a temporary staging table and then use SQL to insert it into the correct places from there.. Dec 17, 2014 at 16:19
  • Either that - or leverage tools OUTSIDE the db to do the job for you (for example, if you're in unix, write a bash script that parses through the CSV and issues INSERT commands via psql).. But I'm pretty sure COPY will be less work. Dec 17, 2014 at 16:20
  • Thank you for your feedback, I have updated my post again. I am searching for an example insert query that looks up the values foreign key ID and insert that instead of the "word"
    – AnC
    Dec 18, 2014 at 12:59
  • Exactly what i need ! Thank your for your patiance.
    – AnC
    Dec 18, 2014 at 20:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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