关于java:postgreSQL的常用语法

postgreSQL 的罕用语法

• 创立数据库表的 sql 语句：

  CREATE TABLE “public”.”tb_test”
  (

    "id" int8 NOT NULL,
    "title"     varchar(50) COLLATE "pg_catalog"."default",
    "ip"        varchar(50) COLLATE "pg_catalog"."default",
    "param"     varchar(500) COLLATE "pg_catalog"."default",
    "result"    varchar(500) COLLATE "pg_catalog"."default",
    "type"      varchar(10) COLLATE "pg_catalog"."default" NOT NULL,
    "create_by" varchar(30) COLLATE "pg_catalog"."default",
    "create_time" timestamptz(6),
    "update_by" varchar(30) COLLATE "pg_catalog"."default",
    "update_time" timestamptz(6),
    "remark"    varchar(500) COLLATE "pg_catalog"."default",
    CONSTRAINT "cgm_third_docking_log_pkey" PRIMARY KEY ("id")

  )
  ;

  COMMENT ON COLUMN “public”.”tb_test”.”id” IS ‘ 主键 ID’;
  COMMENT ON COLUMN “public”.”tb_test”.”title” IS ‘ 模块题目 ’;
  COMMENT ON COLUMN “public”.”tb_test”.”ip” IS ‘ip 地址 ’;
  COMMENT ON COLUMN “public”.”tb_test”.”param” IS ‘ 申请参数 ’;
  COMMENT ON COLUMN “public”.”tb_test”.”result” IS ‘ 返回后果 ’;
  COMMENT ON COLUMN “public”.”tb_test”.”type” IS ‘type’;
  COMMENT ON COLUMN “public”.”tb_test”.”create_by” IS ‘ 创建人 ’;
  COMMENT ON COLUMN “public”.”tb_test”.”create_time” IS ‘ 创立工夫 ’;
  COMMENT ON COLUMN “public”.”tb_test”.”update_by” IS ‘ 更新人 ’;
  COMMENT ON COLUMN “public”.”tb_test”.”update_time” IS ‘ 更新工夫 ’;
  COMMENT ON COLUMN “public”.”tb_test”.”remark” IS ‘ 备注 ’;
  COMMENT ON TABLE “public”.”tb_test” IS ‘test 日志表 ‘;

• 新增表字段的 sql 语句：

  ALTER TABLE tb_test ADD COLUMN dealer_code VARCHAR(20)   COLLATE "pg_catalog"."default";
  COMMENT ON COLUMN "tb_test"."dealer_code" IS '邀约码';
  

• 创立表的惟一索引 sql

  CREATE UNIQUE INDEX uk_tb_test_type ON tb_test(type);
  

• 创立表的一般索引

  create index idx_tb_test_id_ip on tb_test (id desc, ip desc);

• 批改表字段的名称 sql

  alter table tb_test rename column "result" to "Result";
  
  或者 (须要存在 Result 字段)
  
  alter table tb_test rename column "Result" to "result";
  

• 批改表字段的类型 sql

  alter table tb_test alter column id type int8;
  alter table tb_test alter id set not null;
  alter table tb_test alter column pid type int8;
  

• 表增加 jsonb 的字段 sql

  ALTER TABLE tb_test ADD COLUMN followed_other_info jsonb DEFAULT '{}';
  COMMENT ON COLUMN "tb_test"."followed_other_info" IS 'xxxx 其余信息';
  

• 表权限赋予用户 sql

  alter table tb_test owner to xxx_user;

• 插入语句 sql

  INSERT INTO “public”.”tb_region_code” VALUES (100000, ‘ 中国 ’, 0, ‘ 中国 ’, 0, ”, ”, ‘ 中国 ’, 116.368, 39.9151, ‘China’);

• 革除表的全副数据，保留表构造 sql

  truncate table “public”.”tb_test”;

• 批改列的长度的 sql

  ALTER TABLE “public”.”tb_test” ALTER COLUMN “remark” type varchar(300);

• 更新表记录对应列的内容 SQL

  UPDATE tb_test SET title = ‘xxx’ where id = xxxxx

• 删除创立序列的 sql

  DROP SEQUENCE IF EXISTS “public”.”gen_table_column_id_seq”;

• 创立序列的 sql

  DROP SEQUENCE IF EXISTS “public”.”gen_table_id_seq”;
  CREATE SEQUENCE “public”.”gen_table_id_seq”
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

• 删除表构造（含内容）的 sql

  DROP TABLE IF EXISTS “public”.”gen_table_column”;

• 增加主键的 sql

  ALTER TABLE "public"."tb_test" ADD CONSTRAINT "tb_test_id_pkey" PRIMARY KEY ("id");
  

• 查问以后最新 xxx 值距以后工夫小于等于 2h 的数据

  SELECT
      user_id,
      device_id,
      latest_time,
      CURRENT_TIMESTAMP current_value
  FROM
      tb_device  where latest_time between (now()::timestamp + '-2 hour') and now();