Write PostgreSQL Upsert Statements to a File

This example shows how to generate PostgreSQL upsert statements from records and save them to a SQL script instead of running them against a database. PostgreSqlUpsertWriter writes one INSERT ... ON CONFLICT DO UPDATE statement per record: the key fields passed to the constructor name the conflict target, and when a row with that key already exists PostgreSQL updates every column from the EXCLUDED row instead of inserting. The target table needs a unique index or constraint on those columns. The MySQL version of this example is Write MySQL Upsert Statements to a File.

Generating a script instead of executing the statements is useful when the statements must be reviewed, handed to a DBA or replayed in another environment. Values are written as quoted strings here because CSVReader reads every field as text.

Input CSV file

Account,LastName,FirstName,Balance,CreditLimit,AccountCreated,Rating
101,Reeves,Keanu,9315.45,10000.00,1/17/1998,A
312,Butler,Gerard,90.00,1000.00,8/6/2003,B
868,Hewitt,Jennifer Love,0,17000.00,5/25/1985,B
761,Pinkett-Smith,Jada,49654.87,100000.00,12/5/2006,A
317,Murray,Bill,789.65,5000.00,2/5/2007,C

Java Code Listing

package com.northconcepts.datapipeline.examples.cookbook;

import java.io.File;
import java.io.FileWriter;

import com.northconcepts.datapipeline.core.DataReader;
import com.northconcepts.datapipeline.core.DataWriter;
import com.northconcepts.datapipeline.csv.CSVReader;
import com.northconcepts.datapipeline.job.Job;
import com.northconcepts.datapipeline.sql.postgresql.PostgreSqlUpsertWriter;

public class WritePostgreSqlUpsertStatementsToAFile {

    public static void main(String[] args) throws Throwable {
        DataReader reader = new CSVReader(new File("example/data/input/credit-balance-01.csv"))
                .setFieldNamesInFirstRow(true);

        DataWriter writer = new PostgreSqlUpsertWriter("credit_balance",
                new FileWriter("example/data/output/credit-balance-upsert-postgresql.sql"), "Account")
                .setPretty(true);

        Job.run(reader, writer);
    }

}

Code Walkthrough

1. A CSVReader reads credit-balance-01.csv with field names in the first row.
2. A PostgreSqlUpsertWriter is created for the credit_balance table with a FileWriter on example/data/output/credit-balance-upsert-postgresql.sql and Account as the key field, which becomes the ON CONFLICT column. setPretty(true) splits each statement over several lines.
3. Job.run() transfers the records, writing one statement per record and closing the file.

Output SQL file

INSERT INTO "credit_balance" ("Account", "LastName", "FirstName", "Balance", "CreditLimit", "AccountCreated", "Rating")
VALUES ('101', 'Reeves', 'Keanu', '9315.45', '10000.00', '1/17/1998', 'A')
ON CONFLICT ("Account") DO UPDATE SET "Account" = EXCLUDED."Account", "LastName" = EXCLUDED."LastName", "FirstName" = EXCLUDED."FirstName", "Balance" = EXCLUDED."Balance", "CreditLimit" = EXCLUDED."CreditLimit", "AccountCreated" = EXCLUDED."AccountCreated", "Rating" = EXCLUDED."Rating";
INSERT INTO "credit_balance" ("Account", "LastName", "FirstName", "Balance", "CreditLimit", "AccountCreated", "Rating")
VALUES ('312', 'Butler', 'Gerard', '90.00', '1000.00', '8/6/2003', 'B')
ON CONFLICT ("Account") DO UPDATE SET "Account" = EXCLUDED."Account", "LastName" = EXCLUDED."LastName", "FirstName" = EXCLUDED."FirstName", "Balance" = EXCLUDED."Balance", "CreditLimit" = EXCLUDED."CreditLimit", "AccountCreated" = EXCLUDED."AccountCreated", "Rating" = EXCLUDED."Rating";
INSERT INTO "credit_balance" ("Account", "LastName", "FirstName", "Balance", "CreditLimit", "AccountCreated", "Rating")
VALUES ('868', 'Hewitt', 'Jennifer Love', '0', '17000.00', '5/25/1985', 'B')
ON CONFLICT ("Account") DO UPDATE SET "Account" = EXCLUDED."Account", "LastName" = EXCLUDED."LastName", "FirstName" = EXCLUDED."FirstName", "Balance" = EXCLUDED."Balance", "CreditLimit" = EXCLUDED."CreditLimit", "AccountCreated" = EXCLUDED."AccountCreated", "Rating" = EXCLUDED."Rating";
INSERT INTO "credit_balance" ("Account", "LastName", "FirstName", "Balance", "CreditLimit", "AccountCreated", "Rating")
VALUES ('761', 'Pinkett-Smith', 'Jada', '49654.87', '100000.00', '12/5/2006', 'A')
ON CONFLICT ("Account") DO UPDATE SET "Account" = EXCLUDED."Account", "LastName" = EXCLUDED."LastName", "FirstName" = EXCLUDED."FirstName", "Balance" = EXCLUDED."Balance", "CreditLimit" = EXCLUDED."CreditLimit", "AccountCreated" = EXCLUDED."AccountCreated", "Rating" = EXCLUDED."Rating";
INSERT INTO "credit_balance" ("Account", "LastName", "FirstName", "Balance", "CreditLimit", "AccountCreated", "Rating")
VALUES ('317', 'Murray', 'Bill', '789.65', '5000.00', '2/5/2007', 'C')
ON CONFLICT ("Account") DO UPDATE SET "Account" = EXCLUDED."Account", "LastName" = EXCLUDED."LastName", "FirstName" = EXCLUDED."FirstName", "Balance" = EXCLUDED."Balance", "CreditLimit" = EXCLUDED."CreditLimit", "AccountCreated" = EXCLUDED."AccountCreated", "Rating" = EXCLUDED."Rating";