Set the self-increase or cycle method of a column value in a table in PostgreSQL

In postgresql, set the value of an existing column (num) to increase by itself, and you can use the following method:

//Sort the table tb by name, use row_number() over() to query the sequence number and name the column rownum, create a new table tb1 and save the result to the tablecreate table tb1 as (select *, row_number() over(order by name) as rownum from tb); 
//According to the field name of the two tables, update the corresponding value of rownum in tb1 to the num in tb.update tb set num=(select  from tb1 where  = );
//Judge the existence of table tb1 and delete the tabledrop table if exists tb1;

In postgresql, loop to set the existing column (num) value to 0-9, and you can use the following method:

//Sort the table tb by name, use row_number() over() to query the sequence number and name the column rownum, create a new table tb1 and save the result to the tablecreate table tb1 as (select *, row_number() over(order by name) as rownum from tb); 
//According to the field name of the two tables, update the corresponding value of rownum in tb1 to num in tb. Since it is 0-9, the cycle will increase by itself, then %10update tb set num=(select  from tb1 where  = ) % 10;
//Judge the existence of table tb1 and delete the tabledrop table if exists tb1;

Others: Appendix to write a postgresql loop (without related to the above)

 do $$
 declare
 v_idx integer :=0;
 begin
  while v_idx < 10 loop
   update tb set num = v_idx;
   v_idx = v_idx + 1;
  end loop;
end $$;

Supplement: PostgreSQL SQL statement creates self-increment table

Method 1:postgreSQL designs tables as self-increment tables by setting field type to serial

CREATE TABLE t_achievement_directory (
 id serial8 PRIMARY KEY,
 directory_name varchar(255) COLLATE "pg_catalog"."default",
 pid int8,
 modify_time timestamp(6)
)
;

Method 2:GENERATED BY ALWAYS AS IDENTITY or GENERATED BY DEFAULT AS IDENTITY

id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY

id int8 NOT NULL GENERATED ALWAYS AS IDENTITY

The difference between these two methods is:

generated always as identity always insert data in the way (START WITH 1 INCREMENT BY 1) and maintain the index. That is, the user is not allowed to specify data insertion to the id column.

However, generated by default as identity is to insert data in the (START WITH 10 INCREMENT BY 10) method when the user does not specify the id column value. If the user specifies, it will still be inserted according to the specified value.

The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.