CODEWITHSUNDEEP
databasepostgresqljdbclimit
Shannu Vaddi
Shannu Vaddi

Reputation: 113

Postgresql "update query returning *" with limit

Can I run an update query with return clause and also limit the rows returned? For example I run an udate query and it updates a million records, but I dont want to get the updated million rows back to result set.. just a sample say 1000 records. Is this possible?

My Query :

UPDATE table1 SET col1 = value1 RETURNING *

I want to get the number of columns updated and a sample 1000 rows after update.

Upvotes: 4

Views: 4323

Answers (2)

user330315
user330315

Reputation: 

with updated as (
  update the_table_with_many_rows
     set some_column = 42
  where ...
  returning *
)
select u.*, 
       count(*) over () as total_update_count
from updated as u
limit 1000;

Upvotes: 6

Patrick
Patrick

Reputation: 32316

You can do this with a CTE:

WITH updates AS (
  UPDATE my_table SET (f1, f2, f3) = (v1, v2, v3)
  WHERE some_condition
  RETURNING *)
SELECT * FROM updates
LIMIT 1000;

However, you have no control over which 1,000 rows will be returned, unless you can use some ordering in the outer query.

Upvotes: 4

Related Questions