Running PROC COPY In-Database for PostgreSQL

Details about Running PROC COPY for PostgreSQL

Copying data in-database results in faster copy times because the data does not have to be loaded into or moved back out of SAS. You can run the COPY procedure in-database to copy PostgreSQL data in these situations:

• to copy data from one schema to another. In this case, the librefs for the IN= and OUT= options for PROC COPY must match for the SERVER=, PORT=, and DATABASE= values.
• to copy data from one database to another. This case also requires that you have enabled the postgres_fdw extension for the OUT= location. For more information, see your PostgreSQL documentation.

In all cases, to copy data in-database, the USER= who you specify for the source location must have Read permission for that location. Also, the USER= for the target location must have Read permission for the source location and Write permission for the target location.

Here are some points to keep in mind when you copy data in-database:

• You can specify the ACCEL | NOACCEL option in the PROC COPY statement when you copy data. The ACCEL option specifies that data is copied in-database. By default, ACCEL is enabled.
• You can also specify the REPLACE | NOREPLACE system option in the OPTIONS statement when you use the COPY procedure. The REPLACE | NOREPLACE system option specifies whether to replace a data set if it already exists. The REPLACE option is enabled by default.
  Note: When the REPLACE system option is enabled, an existing target table is deleted before PROC COPY attempts to copy data.
• In-database copy is not supported if the input library is a concatenated library. You can copy to a concatenated library if the first target library matches the input library.
• The OVERRIDE= and CLONE | NOCLONE options in the COPY statement are ignored when copying data in-database.
• You can specify PRESERVE_NAMES=YES in the LIBNAME statement to preserve the case of table or column names. This also enables you to copy names with special characters. PRESERVE_NAMES= is an alias for both the PRESERVE_TAB_NAMES= and PRESERVE_COL_NAMES= LIBNAME options. This option is recommended because PostgreSQL is a case-sensitive DBMS.
• Data types that are not supported in SAS Viya, such as BINARY or DECIMAL, are retained when you copy data in-database. If the data is moved into and out of SAS Viya (without copying data in-database), then data types or attributes are converted to types and attributes that are supported in SAS Viya.
• Indexes and integrity constraints are copied with a table when it is copied in-database. These are not copied if the table is moved into and out of SAS Viya (without copying data in-database) during the copy operation.

To verify whether the COPY procedure ran in-database, specify MSGLEVEL=i in the OPTIONS statement.

See Also

• COPY Procedure in Base SAS Procedures Guide
• DATASETS Procedure in Base SAS Procedures Guide
• MSGLEVEL= System Option in SAS System Options: Reference
• REPLACE System Option in SAS System Options: Reference

PROC COPY Examples

This example shows how to copy data in PostgreSQL between schemas in a database. This example copies the table my_table from schema inschema into the schema outsch. The case of the table name is preserved because you specify PRESERVE_NAMES=YES. By specifying the REPLACE system option, you ensure that if the destination table already exists, it is replaced by the copy procedure. The ACCEL option for the PROC COPY statement is enabled by default. This option controls whether data is copied in-database.

libname pgin postgres user="myuser" database=userdb port=5432 
   server="pgserver.com" password=mypwd schema=inschema 
   preserve_names=yes;
libname pgout postgres user="myuser" database=userdb port=5432 
   server="pgserver.com" password=mypwd schema=outsch 
   preserve_names=yes;

options replace;

proc copy in=pgin out=pgout;
   select my_table;
run;
quit;

proc contents data=pgout.my_table; run;

In the following example, you see how to copy table project_tab between different databases. The databases reside on different servers. For the copy to work, the user myuser must have permission to create a schema in the Pgout libref. The ACCEL option for the PROC COPY statement is enabled by default. This option controls whether data is copied in-database.

libname pgin postgres user="myuser" database=userdb port=5432 
   server=pgcloud password=cloudpwd schema=mysch1;
libname pgout postgres user="myuser" database=userpg port=5432 
   server=altpgsvr  password=mypwd2 schema=mysch2;

proc copy in=pgin out=pgout;
  select proj_tab;
run;
quit;

proc print data=pgout.proj_tab; run;