Rows Affected by Last Redshift SQL Query – Examples

  • Post author:
  • Post last modified:July 7, 2020
  • Post category:Redshift
  • Reading time:5 mins read

In many situations, you need to know the number of rows affected by an insert, delete or update query in the current session. The popular relational databases such as SQL Server, Snowflake provides system variables that hold the information of records affected by the last SQL statement. Amazon Redshift just like an Azure synapse date warehouse does not provide any system variables. We have to identify the workaround to get rows affected by the last SQL query in Redshift.

Redshift Rows Affected by Last SQL Query

When you are migrating existing applications such as a Teradata BTEQ scripts to Redshift, you will find it hard to find ACTIVITY_COUNT alternative. The ACTIVITY_COUNT in the Teradata is the number of rows affected by the last SQL statement.

We will use Redshift system tables to retrieve rows affected when you execute statements such as insert, update, delete, etc.

Get Number of Rows Affected in Last Insert query in Redshift

Amazon Redshift maintains insert execution steps for INSERT queries in STL_INSERT system table.

We can query this table to get rows inserted in last insert statement.

For example, consider following query.

select sum(rows) as inserted_rows
from stl_insert 
where query=pg_last_query_id();

Where pg_last_query_id() function returns the query ID of last executed query.

Get Number of Rows Affected in Last Delete query in Redshift

Amazon Redshift maintains delete execution steps for DELETE queries in stl_delete system table.

We can query this table to get rows deleted in last delete statement.

For example, consider following query.

select sum(rows) as deleted_rows
from stl_delete 
where query=pg_last_query_id();

Get Number of Rows Affected in Last Update query in Redshift

In redshift, there is no update operation behind the scene. All update statements goes as a delete the record to be updated and insert the updated record as new a record.

We can use stl_insert and stl_delete system tables to get updated record count/


SELECT 
       CASE
         WHEN nvl (Deleted_Rows,0) = nvl (Inserted_Rows,0) THEN nvl (Inserted_Rows,0)
         ELSE 0
       END Updated_Rows
FROM (SELECT query,
             relname,
             SUM(ROWS) Deleted_Rows
      FROM stl_delete sd
        JOIN pg_class t ON t.oid = sd.tbl
      GROUP BY query,
               relname) d
  FULL JOIN (SELECT query,
                    relname,
                    SUM(ROWS) Inserted_Rows
             FROM stl_insert si
               JOIN pg_class t ON t.oid = si.tbl
             GROUP BY query,
                      relname) i ON i.query = d.query
WHERE CASE WHEN d.query IS NOT NULL THEN d.query ELSE i.query END = pg_last_query_id();

ROW_COUNT in Redshift Stored Procedure

ROW_COUNT shows the number of rows processed by the last SQL command sent down to the SQL engine. The ROW_COUNT is used with the GET DIAGNOSTICS command.

For examples, consider following Redshift stored procedure.

CREATE OR REPLACE PROCEDURE sp_row_count() AS
$$
DECLARE
  integer_var int;
BEGIN
  INSERT INTO tbl_row_count VALUES(1);
  GET DIAGNOSTICS integer_var := ROW_COUNT;
  RAISE INFO 'rows inserted = %', integer_var;
END;
$$ LANGUAGE plpgsql;

Hope these queries will help you. Do let me know in the comments if you are using different methods.

Tags: ,