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

the db table looks like this:

text_id serial PRIMARY KEY,
topic_id integer REFERENCES topics,
format_id integer REFERENCES formats,
text text

Content: 1;1;1;sample text

topic_id serial PRIMARY KEY,
topic varchar(50) NOT NULL

Content: 1;comedy

format_id serial PRIMARY KEY,
format varchar(50) NOT NULL

Content: 1;A5

and an csv sheet which looks like this:


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.


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


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.



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.

  • 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 you have read our privacy policy.

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