Search code examples
amazon-redshiftidentity-column

How can I capture the number of rows inserted into a Redshift table which contains an identity column?

In Amazon Redshift, I'm using the following query to capture the number of rows inserted by the last query: 

SELECT SUM(rows) FROM stl_insert WHERE query = pg_last_query_id();

It seems that this approach doesn't work when I insert into a table that contains an IDENTITY column, because Redshift also runs this query: SELECT * FROM stv_identity_highwater and uses this query's id for pg_last_query_id().

Is there another way to capture the number of rows inserted in this scenario?

Solution

  • For inserting values in table with identity column Redshift fires a query internally as SELECT * FROM stv_identity_highwater so the count result is 0.

    Add pg_last_query_id()-1to get the insert count:

    SELECT SUM(rows) FROM stl_insert WHERE query = pg_last_query_id()-1;