postgreSQL的常用语法

JAVA_IN_LIFE

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();
    
阅读 173

java in life

1 声望
0 粉丝
0 条评论
你知道吗?

java in life

1 声望
0 粉丝
宣传栏