pg數(shù)據(jù)庫增加自增序列和對已有數(shù)據(jù)插入自增值
pg數(shù)據(jù)庫增加自增序列
pg數(shù)據(jù)庫增加自增序列步驟：
#增加自增序列
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ù)庫表時設(shè)置id為自增序列看成。
create table sjy_water_quality(
id serial)
對已有數(shù)據(jù)插入自增值
利用pg函數(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ù)實現(xiàn)參考 postgreSQL存儲過程寫法示例
