pg數(shù)據(jù)庫(kù)增加自增序列和對(duì)已有數(shù)據(jù)插入自增值

pg數(shù)據(jù)庫(kù)增加自增序列

pg數(shù)據(jù)庫(kù)增加自增序列步驟：

#增加自增序列
CREATE SEQUENCE sjy_water_quality_id_seq
    INCREMENT 1
    START 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 2;```
#增加鍵id
alter table sjy_water_quality add column id int;
#修改鍵id為自增序列
alter table sjy_water_quality alter column id set default nextval('sjy_water_quality_id_seq');

或者創(chuàng)建數(shù)據(jù)庫(kù)表時(shí)設(shè)置id為自增序列。

create table sjy_water_quality(
id serial)

對(duì)已有數(shù)據(jù)插入自增值

利用pg函數(shù)，實(shí)現(xiàn)已有數(shù)據(jù)插入自增值。
pg函數(shù)如下

CREATE OR REPLACE FUNCTION increment() RETURNS integer AS $$
DECLARE
r RECORD; 
num int4 := 0;  
sql "varchar";  
        BEGIN
                    sql := 'select id,stationname,datetime from sjy_water_quality';
                    FOR r IN EXECUTE sql LOOP  
                        num := num + 1; 
                        update sjy_water_quality set id = num where stationname = r.stationname and datetime = r.datetime;
                    END LOOP; 
                    RETURN num;
        END;

$$ LANGUAGE plpgsql;

pg函數(shù)調(diào)用

select * from increment();

pg函數(shù)實(shí)現(xiàn)參考 postgreSQL存儲(chǔ)過程寫法示例