postgres建表语句、pg自增主键、批量存在则更新、创建索引、中文排序、自增序列

自增主键ID(2种方案)

1. 采用pg自带的序列: serial、bigserial，每次自增1

   -- 字段写法，PRIMARY KEY可以放最后
   "id" bigserial NOT NULL PRIMARY KEY,
   

2. 采用自增序列，可自定义自增步长（每次自增5、100都可以）

   -- 若已存在bs_user_id_seq则先删除
   DROP SEQUENCE IF EXISTS public.bs_user_id_seq CASCADE;
   -- 创建自增序列
   CREATE SEQUENCE public.bs_user_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
   -- 字段写法
    "id" int8 NOT NULL  DEFAULT nextval('bs_user_id_seq') PRIMARY KEY,
   

完整建表sql语句

-- 若已存在bs_user_id_seq则先删除
DROP SEQUENCE IF EXISTS public.bs_user_id_seq CASCADE;
-- 创建自增序列
CREATE SEQUENCE public.bs_user_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-- 若存在表bs_user则删除
DROP TABLE IF EXISTS "public"."bs_user";
-- 建表
CREATE TABLE "public"."bs_user" (
  "id" int8 NOT NULL  DEFAULT nextval('bs_user_id_seq'),
  -- 或者"id" bigserial NOT NULL,
  "username" varchar(64) DEFAULT '',
  "nickname" varchar(64) DEFAULT '',
  "password" varchar(64) DEFAULT '',
  "mobile" varchar(32) DEFAULT '',
  "token" varchar(2048) DEFAULT '',
  "email" varchar(64) DEFAULT '',
  "del_flg" int2 DEFAULT 1,
  "remark" varchar(2048) DEFAULT NULL,
  -- 时间用long毫秒值或秒值
  "create_time" int8 DEFAULT 0,
  "update_time" int8 DEFAULT 0,
  -- 一定要用时间戳可以这样定义
  "create_at" timestamp DEFAULT CURRENT_TIMESTAMP,
  -- 主键：可以多个，用逗号隔开
	PRIMARY KEY(id)
);
-- 创建索引(以username,del_flg为例)
CREATE INDEX IF NOT EXISTS "bs_user_username_del_flg" ON "public"."bs_user" USING btree (
    "username","del_flg"
    );
-- 设置列对应描述
COMMENT ON COLUMN "public"."bs_user"."id" IS '主键ID';
COMMENT ON COLUMN "public"."bs_user"."username" IS '用户名';
COMMENT ON COLUMN "public"."bs_user"."nickname" IS '昵称';
COMMENT ON COLUMN "public"."bs_user"."password" IS '密码';
COMMENT ON COLUMN "public"."bs_user"."mobile" IS '手机号';
COMMENT ON COLUMN "public"."bs_user"."token" IS 'token';
COMMENT ON COLUMN "public"."bs_user"."email" IS '邮箱';
COMMENT ON COLUMN "public"."bs_user"."del_flg" IS '状态（0:删除,1:正常）';
COMMENT ON COLUMN "public"."bs_user"."remark" IS '备注';
COMMENT ON COLUMN "public"."bs_user"."create_time" IS '创建时间';
COMMENT ON COLUMN "public"."bs_user"."update_time" IS '更新时间';
-- 设置表描述
COMMENT ON TABLE "public"."bs_user" IS '后台用户表';

说明：create_time采用long型时间戳故用long型保存
时间格式可以使用timestamp，示例"create_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP

tips(避坑):

• 不建议使用char，原因是char类型会用空格填充字段，达到指定长度，例如 建表时username char(12)，插入数据admin，则表中存储的数据为admin+7个空格，构成12位

插入数据（存在则更新,等同mysql中on duplicate key）

   <!-- 入参为List<McGoods> -->
   <update id="batchSaveOrUpdate">
        INSERT INTO `mc_goods`(`goods_id`, `goods_name`,`goods_quantity`, `is_onsale`)
        VALUES
        <foreach collection="list" item="l" separator=",">
            (#{l.goodsId},#{l.goodsName},#{l.goodsQuantity},#{l.isOnsale})
        </foreach>
        <!-- goods_id为主键 -->
        on conflict(goods_id) do update set
        <!-- excluded.表示取传入的参数值 -->
        goods_quantity = excluded.goods_quantity,
        is_onsale = excluded.is_onsale
    </update>

中文排序

select age,name,address
	from person
	where age > 10
	order by convert_to(name,'GBK') asc,convert_to(address,'GBK') asc LIMIT 20