How to update null values with sequence number?-CodePudding

I have a table like this

id val
1  null
2  10
3  null
4  null
5  7

I want to get

I tried to do it like this

CREATE SEQUENCE new_val START 1

UPDATE tb1
SET val = new_val
WHERE val is null

But I get an error that new_val doesn't exist

CodePudding user response：

You need to use nextval() and provide the sequence name as a string:

CREATE SEQUENCE new_val 1;

UPDATE tb1
  SET val = nextval('new_val')
WHERE val is null;

Another option is to use row_number()

UPDATE tb1
  SET val = t.rn
FROM (
   select id, row_number() over (order by id) as rn
   from tb1
)
WHERE tb1.val is null
  and tb1.id = t.id;

id is assumed to be the primary key of the table.

CodePudding user response：

You get the next value of a sequence with the nextval function. The function takes regclass as argument type, for which you can supply the name of the sequence (as single quoted string) or the object identifier:

nextval('new_val')