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

Question

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?

Answer 1

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;

Answer 2

You could use the STL_QUERY system table to extract the ID of the query you wish to count.

If you are the only user of the system, you could assume that you the second-last query is the one you seek.

Answer 3

Unfortunately this will not work :

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

as query_id's are not contiguos in Redshift.

The way it can be retrieved for a single table is :

      WITH last_queryid_for_table AS (

           SELECT query, MAX(si.starttime) OVER () as last_q_stime, si.starttime as stime  FROM stl_insert si,
           SVV_TABLE_INFO sti WHERE sti.table_id=si.tbl AND sti."table"='$your_table_name' 
)

           SELECT SUM(rows) FROM stl_insert si, last_queryid_for_table lqt 
           WHERE si.query=lqt.query AND lqt.last_q_stime=stime

• Ofcourse you should take into account if the query is failied or not. If last result is failing the above query will give you the most recent execution stats.