COPY FROM example - cyanogilvie/pgwire GitHub Wiki

COPY FROM allows efficient loading of bulk data into a table. Pgwire exposes this as a channel that is valid for a block of Tcl script to which the script writes the data in CSV or text format, as specified in the sql query:

package require csv     ;# From tcllib

pg copy_from h {
    copy example_table (id, name) from stdin (format csv)
} {
    for {set id 1} {$i <= 1000} {incr i} {
        puts $h [csv::join [list $id "Name: $id"]]
    }
}

or in the default text format, with every 10 th row's name column containing NULL:

pg copy_from h {
    copy example_table (id, name) from stdin
} {
    for {set id 1} {$i <= 1000} {incr i} {
        puts $h "$id\t[expr {$id % 10 == 0 ? {\N} : "Name: $id"}]"
    }
}

The text example omits properly escaping the tab-separated values it sends for clarity in the example, and because the value construction rules only permit valid values that don't require escaping. In general the values should be escaped as described on the COPY page.

If you have a file with the table data which is already in the format required, it can be efficiently loaded using the chan copy command:

set in [open data.csv r]
try {
    pg copy_from tab {
        copy example_table (id, name) from stdin (format csv)
    } {
        chan copy $in $tab
    }
} finally {
    close $in
}