Return number of rows inserted and insert into log table

Question

I have an INSERT statement where I want to copy contents of one table to the other, and want to write the number of rows inserted into a log table. I tried this:

with upd as (
    insert into tb1 OVERRIDING USER VALUE  
    select * from tb2
    returning count(*) as num_rows_inserted
)
insert into month_log select * from upd;

But Postgres complains:

ERROR:  aggregate functions are not allowed in RETURNING
LINE 4:  returning count(*) as num_rows_inserted
                   ^
SQL state: 42803
Character: 109

How can I return the number of rows without an aggregate?

Erwin Brandstetter · Accepted Answer · 2023-03-16T06:57:30.093

Like the error msg says:

aggregate functions are not allowed in RETURNING

You must do the counting in the next level. Like:

WITH ins1 AS (
   INSERT INTO tb1 OVERRIDING USER VALUE
   SELECT * FROM tb2
   RETURNING 1  -- for counting a constant is good enough
)
INSERT INTO month_log (count_column)  -- spell out target column!
SELECT count(*) FROM ins1;

Or use GET DIAGNOSTICS in a PL/pgSQL code block to avoid the extra work. Like:

DO
$do$
DECLARE
   _row_ct int;
BEGIN
   INSERT INTO tb1 OVERRIDING USER VALUE
   SELECT * FROM tb2;

   GET DIAGNOSTICS _row_ct := ROW_COUNT;
   
   INSERT INTO month_log (count_column)
   VALUES (_row_ct);
END
$do$;

Return number of rows inserted and insert into log table

1 Answers1