Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

PG_LAST_COPY_ID()

Returns the query ID of the most recently executed COPY command in the current session. If no COPY commands have been executed in the current session, PG_LAST_COPY_ID returns -1.

The value for PG_LAST_COPY_ID is updated when the COPY command begins the load process. If the COPY fails because of invalid load data, the COPY ID is updated, so you can use PG_LAST_COPY_ID when you query STL_LOAD_ERRORS table. If the COPY transaction is rolled back, the COPY ID is not updated.

The COPY ID is not updated if the COPY command fails because of an error that occurs before the load process begins, such as a syntax error, access error, invalid credentials, or insufficient privileges. The COPY ID is not updated if the COPY fails during the analyze compression step, which begins after a successful connection, but before the data load. COPY performs compression analysis when the COMPUPDATE parameter is set to ON or when the target table is empty and all the table columns either have RAW encoding or no encoding.

Syntax

Copy
pg_last_copy_id()

Return Type

Returns an integer.

Example

The following query returns the query ID of the latest COPY command in the current session.

Copy
select pg_last_copy_id(); pg_last_copy_id --------------- 5437 (1 row)

The following query joins STL_LOAD_ERRORS to STL_LOADERROR_DETAIL to view the details errors that occurred during the most recent load in the current session:

Copy
select d.query, substring(d.filename,14,20), d.line_number as line, substring(d.value,1,16) as value, substring(le.err_reason,1,48) as err_reason from stl_loaderror_detail d, stl_load_errors le where d.query = le.query and d.query = pg_last_copy_id(); query | substring | line | value | err_reason -------+-------------------+------+----------+---------------------------- 558| allusers_pipe.txt | 251 | 251 | String contains invalid or unsupported UTF8 code 558| allusers_pipe.txt | 251 | ZRU29FGR | String contains invalid or unsupported UTF8 code 558| allusers_pipe.txt | 251 | Kaitlin | String contains invalid or unsupported UTF8 code 558| allusers_pipe.txt | 251 | Walter | String contains invalid or unsupported UTF8 code