Import CSV

Postgres has this ability to create a temporary table that you can setup for staging purposes. Before you actually commit to the database it’s good practice to look at this data in a temporary table.

In this example below I have a .csv I would like to import. Let’s create a temporary table to review.

Create Temporary Table

-- CREATE Temporary TABLE
CREATE TEMP TABLE tmp_x (speaker_id int, speaker_name text, twitter text, website text);

Import CSV into the temporary table

-- IMPORT TABLE
COPY tmp_x FROM '/Users/myusername/Desktop/speakers.csv' delimiter ',' csv header;  

At this point you can query for the table like it was a regular table

SELECT * FROM tmp_x;

Update

We can update the table by referencing the tmp_x that we set up earlier.

-- UPDATE
UPDATE
    speakers
SET
    website = tmp_x.website, twitter = tmp_x.twitter
FROM
    tmp_x
WHERE
    speakers.id = tmp_x.speaker_id;

Disappears

The tmp_x that we set up earlier will disappear once you close your psql session.