Importing/exporting Postgres/CSV

-- Temporary table for importing raw CSV
create table tmp (
    id     serial PRIMARY KEY,
    topic  varchar(255),
    format varchar(255),
    text   text
);


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

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

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

-- psql client command to copy CSV to tmp table
\COPY tmp(topic, format, text) FROM 'test.csv' DELIMITER ',' CSV HEADER;

-- Create master tables
INSERT INTO topics (topic) SELECT DISTINCT topic FROM tmp;
INSERT INTO formats (format) SELECT DISTINCT format FROM tmp;

-- Create the final data table, join _id from master tables
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;

-- Clean up
DROP TABLE tmp;