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?