postgresql常用语句

最近接触了pgsql,由于是关于将sqlserver迁移到pgsql过程中，在开发过程中，遇到的一些关于pgsql语句简单实用语句，供初学者借鉴

1.自增

1 CREATE SEQUENCE public.tablename_id_seq
2     INCREMENT 1
3     START 1
4     MINVALUE 1
5     MAXVALUE 9223372036854775807
6     CACHE 1;

 

2.大写改小写

ALTER TABLE "public"."tableName" RENAME COLUMN  "UnShared" To "unshared";

 

3.表明注释

1 COMMENT ON TABLE "public"."tableName" IS '工资等级（绩效、薪资等）';

 

4.创建表带有索引自增

1 CREATE TABLE "public"."tableName" (
2 "id" serial PRIMARY KEY,
3 "minvalue" float8,
4 "maxvalue" float8
5 )
6 ;

 

5.添加主键

1 ALTER TABLE "public"."tableName" 
2 ADD PRIMARY KEY ("id");

 

6.强制加自增(这个有借鉴别人写的)

1 ALTER TABLE tablename ALTER COLUMN id SET DEFAULT null;
2 DROP SEQUENCE IF EXISTS tablename_id_seq;
3 -- 这里的 id_max 即 id 目前的最大值，可通过 “SELECT MAX(id) FROM tablename” 得到
4 -- CREATE SEQUENCE tablename_id_seq START WITH id_max;
5 -- SELECT MAX(id) FROM tablename
6 -- SELECT MAX(id) FROM tablename
7 CREATE SEQUENCE tablename_id_seq START WITH 38;
8 ALTER TABLE tablename ALTER COLUMN id SET DEFAULT nextval('tablename_id_seq'::regclass);

 

7.主键自增重新设置

1 SELECT setval('"tablename_id_seq"', (SELECT MAX("id") FROM "tablename"));

 

8.添加字段

1 ALTER TABLE "public"."tableName" ADD COLUMN "code" varchar(255);

 

9. 设置不能为空

1 ALTER TABLE "public"."tableName" ALTER COLUMN "code" SET NOT NULL;

 


10.设置唯一键

1 alter table "public"."tableName" add constraint "unique_code_sid" unique ("code");

 

 11.设置一个主键是int8（长度64）的自增主键

 1 CREATE TABLE "public"."tableName" (
 2   "id" int8 PRIMARY KEY,
 3   "minvalue" float8,
 4   "maxvalue" float8
 5 )
 6 ;
 7 
 8 CREATE SEQUENCE tableName_id_seq 
 9     START WITH 1 
10     INCREMENT BY 1 
11     NO MINVALUE 
12     NO MAXVALUE 
13     CACHE 1; 
14     
15 ALTER TABLE "public"."tableName" ALTER COLUMN id SET DEFAULT nextval('tableName_id_seq'::regclass);

 

12.删除主键

alter table tableName drop constraint tableName_pkey;

 

13.添加复合主键

ALTER TABLE "tableName" 
  ADD PRIMARY KEY ("jsid","peid");

 

14.修改表字段类型长度（没有改变类型）

1 alter table skypayitemobjs alter column  remark set data TYPE VARCHAR(5000);

 

15.表字段名备注

1 -- 表字段备注
2 COMMENT ON    column      suggest.id        is    '主键';

 

16.把int4类的主键改成int8

ALTER TABLE "public"."tableName" 
  ALTER COLUMN "id" TYPE int8 USING "id"::int8;

 17.建表时，主键int8自增

"id" bigserial PRIMARY KEY

18.创建默认时间

"modify_time" timestamp(6) default now()

 

posted @ 2018-11-22 19:37  golconda  阅读(3941)  评论(0)  编辑  收藏  举报