Postgresql csv to db translate foreign keys

Question

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?

Answer 1

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;