postgres 序列

postgres序列(serial)和类型:https://www.cnblogs.com/alianbog/p/5654604.html

序列:https://www.cnblogs.com/mchina/archive/2013/04/10/3012493.html

serial序列其实就是当不给此字段赋值的时候,自动获取下一个值(唯一),可以当作自增主键

一、 创建表的时候创建序列

1. 方式一

create table tbl_serial(a serial,b varchar(2));

2. 方式二

DROP SEQUENCE if EXISTS "public"."quake_data_id_seq";  
CREATE SEQUENCE "public"."quake_data_id_seq"  
 INCREMENT 1  
 MINVALUE 1  
 MAXVALUE 9223372036854775807  
 START 1  
 CACHE 1;
 
DROP TABLE if EXISTS "public"."quake_data"; 
CREATE TABLE "public"."quake_data" (
  "id" int4 NOT NULL DEFAULT nextval('quake_data_id_seq'::regclass),
  "quake_id" int4 DEFAULT 0,
  "channel_id" varchar(40) COLLATE "pg_catalog"."default" DEFAULT NULL,
  "gauge_data" float8 DEFAULT NULL,
  "guage_time" timestamp(6) DEFAULT NULL,
  CONSTRAINT "1" PRIMARY KEY ("id")
)
;

ALTER TABLE "public"."quake_data" 
  OWNER TO "username";

COMMENT ON COLUMN "public"."quake_data"."quake_id" IS '事件id';
COMMENT ON COLUMN "public"."quake_data"."channel_id" IS '点号';
COMMENT ON COLUMN "public"."quake_data"."gauge_data" IS '测量值';
COMMENT ON COLUMN "public"."quake_data"."guage_time" IS '测量时间';

 

二、表已经创建好,后来单独创建序列,给表字段绑定序列

创建序列示例:

CREATE SEQUENCE "public"."quake_data_id_seq"  
 INCREMENT 1  
 MINVALUE 1  
 MAXVALUE 9223372036854775807  
 START 1  
 CACHE 1;

修改字段默认值:

1. 使用的navicat

 

 2. 使用ALTER命令

ALTER TABLE "public"."ttt" 
  ALTER COLUMN "id" TYPE int4 USING "quake_id"::int4;

ALTER TABLE "public"."ttt" 
  ALTER COLUMN "id" SET DEFAULT nextval('ttt_id_seq'::regclass);

PS: 具体alter命令可以取官方网站查询:https://www.postgresql.org/docs/9.1/sql-altertable.html

 

小技巧:

还有一个方便的方法:使用navticat修改一个字段(属性,添加字段,删除字段等等都可以),然后点击SQL预览,就可以看到对应的sql命令了

比如下面图片演示,更改guage_time字段不能为空

然后点击SQL预览, 显示sql语句:

ALTER TABLE "public"."ttt" 
  ALTER COLUMN "guage_time" SET NOT NULL;

 

posted @ 2019-09-04 17:15  静静别跑  阅读(971)  评论(0编辑  收藏  举报