5.13. Example - COPY FROM and COPY TO

5.13. Example - COPY FROM and COPY TO
Prev	Chapter 5. Example Programs

These examples show how to use SQL COPY to perform bulk transfers to and from a database table. For more information on COPY, refer to the PostgreSQL Reference Manual.

Note

This example does not work with pgintcl, which uses special commands to read and write during COPY. Refer to the pgintcl documentation for details.

The first example copies data into a table. After sending the SQL COPY command, tab-separated data is written directly to the database connection handle, one record per line. End of data is signaled by sending a line with a backslash followed by a dot, written in Tcl as "\\.". This converts the connection back to its normal mode, and the final status of the copy can be verified.

Example 5.20. COPY TO

  # Send the SQL COPY command and check initial status:
  set res [pg_exec $conn "COPY mytable FROM STDIN"]
  if {[pg_result $res -status] != "PGRES_COPY_IN"} {
    set message [pg_result $res -error]
    pg_result $res -clear
    error $message
  }

  # Write to the database:
  for {set i 0} {$i < 100} {incr i} {
    puts $conn "$i\tThis is data line $i"
  }

  # Write the end of data marker:
  puts $conn "\\."

  # Check final status:
  if {[pg_result $res -status] != "PGRES_COMMAND_OK"} {
    set message [pg_result $res -error]
    pg_result $res -clear
    error $message
  }
  pg_result $res -clear

The second example copies data from a table. After sending the SQL COPY command, tab-separated data is read directly from the database connection handle, one record per line. When Tcl sees End of File on the database connection handle channel, the copy is complete, the connection is back in its normal mode, and the final status of the copy can be verified.

Example 5.21. COPY FROM

  # Send the SQL COPY command and check initial status:
  set res [pg_exec $conn "COPY mytable TO STDOUT"]
  if {[pg_result $res -status] != "PGRES_COPY_OUT"} {
    set message [pg_result $res -error]
    pg_result $res -clear
    error $message
  }

  # Read until EOF
  while {[gets $conn line] >= 0} {
    puts "Read line: $line"
  }

  # Check final status:
  if {[pg_result $res -status] != "PGRES_COMMAND_OK"} {
    set message [pg_result $res -error]
    pg_result $res -clear
    error $message
  }
  pg_result $res -clear

Prev	Up
5.12. Example - Asynchronous Query with Event-Driven Results	Home