Postgres - Use temporary table to query from CSV

If you need to compare the contents of a large CSV file the to data in your existing database, you might want to take advantage of temporary tables. A temporary table exists only for the duration of the database session or transaction in which it is created. These tables can be useful for storing intermediate or temporary data that you need to compare to your live data but don’t want to persist in the database permanently.

Let’s look at an example:

CREATE TEMPORARY TABLE temp_uploaded_orders (
  order_id INT,
  store_id TEXT,
  settled BOOLEAN
);

Assuming you have a CSV file that looks like and let’s call it patch-orders.csv

order_id,store_id,settled
149680006,3b396a737dq6rqWVYoRI4RBLNq7auIYzw,f
145845933,3b396a737d94_5fIIWzTYRpWa0U5fKCvw,f
263117262,3b396a737dM3o6Mq35zJQRJVZsZjv9cPo,t
163675451,3b396a737d7_kO4JOptZ2d32rLcbsb_82o,t
264815430,3b396a737d52apArXRx0cRtZG9vxIbLcU,f
262099729,3b396a737dhw6ji_-97Z2d3kqiTAADftL4,f
260472101,3b396a737d1_s1r021hkcRMYNNp21oios,t

Create the temp table

Now we will log into our psql & create the table:

kenglish=# CREATE TEMPORARY TABLE temp_uploaded_orders (
kenglish(#   order_id INT,
kenglish(#   store_id TEXT,
kenglish(#   settled BOOLEAN
kenglish(# );
CREATE TABLE

Copy csv data into temp table

Next you can copy the csv into this table via the COPY command:

kenglish=# \copy temp_uploaded_orders from '/Users/kenglish/Downloads/patch-orders.csv' csv header
COPY 7

Export data to a csv file using temp in a query

Now, suppose you want to extract the uuids and some other values from your customers table with the following query:

(with customer_ids as  (
    select orders.customer_id as customer_id 
    FROM orders  
    INNER JOIN temp_uploaded_orders ON orders.id = temp_uploaded_orders.order_id  
    WHERE settled=true
)  SELECT id, uuid as customer_uuid, created_at 
     FROM customers  
    WHERE customers.id in (select customer_id from customer_ids))

This can now be exported to your local directory using the the same COPY command. You probably need to collapse the query onto one line:

kenglish=# \copy (with customer_ids as  (SELECT orders.customer_id as customer_id FROM orders  INNER JOIN temp_uploaded_orders ON orders.id = temp_uploaded_orders.order_id  WHERE settled=true)  SELECT id, uuid as customer_uuid, created_at FROM customers WHERE customers.id in (select customer_id from customer_ids)) to '/Users/kenglish/Downloads/customers-for-ops.csv' csv header;
COPY 8578903

Now when you logout of psql the table temp_uploaded_orders will be obliterated.