postgreSQL的常用语法

2021-10-09

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");
【postgreSQL的常用语法】查询当前最新xxx值距当前时间小于等于2h的数据

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

推荐阅读

上一篇：【图文详解】canvas插件安装方法及常用插件推荐

下一篇：购物网站多语言架构