最近接触了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");

 

 

版权声明：本文为baoshan原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/baoshan/p/10003418.html