Bulk Loading and Unloading for PostgreSQL

Overview of Bulk Loading and Unloading for PostgreSQL

Bulk loading is the fastest way to insert large numbers of rows into a PostgreSQL table. To use the bulk-load utility, set the BULKLOAD= data set option to YES. Similarly, bulk unloading is the fastest way to retrieve large numbers of rows from a PostgreSQL table. To use the bulk-unloading utility, set the BULKUNLOAD= data set option to YES. Bulk loading and unloading are implemented with the PostgreSQL PSQL utility, which moves data between SAS and the PostgreSQL database.

Data Set Options with Bulk Loading and Unloading

Here are the PostgreSQL data set options for bulk loading and bulk unloading. For more information about these options, see Overview.

• BL_DATAFILE=
• BL_DEFAULT_DIR=
• BL_DELETE_DATAFILE=
• BL_DELIMITER=
• BL_ESCAPE=
• BL_FORMAT=
• BL_LOAD_METHOD=
  Note: This data set option applies to bulk loading only.
• BL_LOGFILE=
• BL_NULL=
• BL_PSQL_PATH=
• BL_QUOTE=
• BULKLOAD=
• BULKUNLOAD=

Using the PSQL Tool for Bulk Loading and Unloading

Required for bulk loading and bulk unloading, the PSQL tool is a terminal-based front end to PostgreSQL. You can use it to enter queries interactively, submit them to PostgreSQL, and see the query results. You can also submit a file as input. PSQL provides a number of metacommands and various shell-like features to facilitate writing scripts and automating various tasks. It is available here: http://www.postgresql.org.

Bulk-Load Example for PostgreSQL

This first example shows how you can use a SAS data set, SASFLT.FLT98, to create and load a large PostgreSQL table, FLIGHTS98:

libname sasflt 'SAS-library';
libname net_air postgres user=myusr1 pwd=mypwd1
        server=air2 database=flights;

proc sql;
create table net_air.flights98 (bulkload=YES bl_psql_path='full-path-of-PSQL')
        as select * from sasflt.flt98;
quit;