PostgreSQL: How to Limit Rows in Update Statement

postgresqlupdate

My table employees contain more than ten million data. i want to update 10k rows in commission_pct column where commission_pct is null and set zero(0) value.

in oracle I can easily solve this by using rownum.

update employees
set commission_pct=0
where commission_pct is null and rownum<=10000;

but postgresql does not support rownum.

how to solve this in postgresql?

Best Answer

You need to search for the desired rows with a subquery, and use the primary key of the table to relate those rows to the table in the UPDATE statement.

In general, rownum can be replaced with the row_number() window function (see, e.g., Using window functions in an update statement), but for this case, it is easier to just use limit:

UPDATE employees
SET commission_pct = 0
WHERE id IN (SELECT id
             FROM employees
             WHERE commission_pct IS NULL
             LIMIT 10000);

Related Solutions

PostgreSQL – How to Perform Bulk Update of All Columns

If you only want to update data, I'm not sure what the INSERT statement is for in your question.

If you just want to update several rows with a single statement, you might be looking for this:

with update_values (ID,PARENT_ID,BOUGHT_IN_FORM_TYPE_ID,PRIORITY,NAME,HEADING,DESCRIPTION,ICON,BOUGHT_IN_CONTROL_PANEL_FILE_ID) as 
(
  VALUES
     (109,1,28,100,'Tooling','Tooling','Enter your Machine Tools here','tooling.png',null), 
     (1,0,1,200,'Bought In','Bought In','','boughtin.png',null)
)
update bought_in_control_panel
   set parent_id = ud.parent_id, 
       bought_in_form_type_id = ud.bought_in_form_type_id,
       ....
from update_values ud
where ud.id = bought_in_control_panel.id;

PostgreSQL Update – Most Efficient Way to Perform Multiple Updates

If you don't want to interfere with other activity, the one UPDATE at a time in autocommit mode is very likely the best option. You should probably set synchronous_commit=off in that session (and only that session).

The indexes are going to slow you down, perhaps by a lot depending on your RAM and your IO system. But if the index is necessary for the other actions you don't want to interfere with, then there isn't anything you can do about it.

But since the fid is not yet correctly populated, the index on it is probably not actually useful to the concurrent processes you want avoid interfering with, as they haven't been changed yet to rely on that column being accurate. If that is the case, you can drop that index to gain speed, and build it in bulk later. The same probably applies to the foreign key constraint.

Once that index is gone, your updates can proceed via HOT (Heap Only Tuples) updates provided each block has enough free space. In that case, the updates will not have to do maintenance on the primary key index, either, saving that much more IO. To maximize the likelihood that this will work optimally, it is important that each UPDATE be its own transaction. That way one UPDATE can reuse space freed up by an earlier one.

Also, your WHERE clause should probably be like:

WHERE id=345 and fid is not null;

That way if the script gets interrupted, you can re-run it with minimal damage.

Since you seem to be running this on a test system already, then an EXPLAIN (ANALYZE,BUFFERS) of some of the updates would be helpful, especially with track_io_timing set to on.

Best Answer

Related Solutions

PostgreSQL – How to Perform Bulk Update of All Columns

PostgreSQL Update – Most Efficient Way to Perform Multiple Updates

Related Question