COPY - potatoscript/sql GitHub Wiki

• COPY data into the temporary table
• The following command will create a temporary table from the main_table without data from the main_table

CREATE TEMP TABLE tm_table
ON COMMIT DROP
AS
SELECT * FROM main_table
WITH NO DATA;

• The following command will insert the tmp_table data into main_table with unique data only

COPY tmp_table FROM "c:\MyProject\test.csv" DELIMITER ',' csv HEADER;
INSERT INTO main_table
SELECT DISTINCT ON(field1) * FROM tmp_table ORDER BY (field1)

or

INSERT INTO main_table 
SELECT * FROM tmp_table ON CONFLICT DO NOTHING

• The following command will check the value between the tmp_table and main_table to prevent inserting the existing data again into the main_table

INSERT INTO main_table
SELECT * FROM tmp_table t 
WHERE NOT EXISTS(SELECT 1 FROM main_table e WHERE e.field1 = t.field1)

• ※Note that this might still generate errors if done from multiple concurrent transactions. So it is mofe suited for a one time bulk load.