How to limit rows in PostgreSQL update statement

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?

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);

StackExchange Database Administrators Q#213147, answer score: 23