pgsql基础

 

--创建uuid 扩展

create extension "uuid-ossp";

--查看版本
SELECT version();
--创建uuid 扩展
create extension "uuid-ossp";
--生成一个uuid
SELECT uuid_generate_v4();
SELECT * from abc where body.key="22"
SELECT
info -> 'customer' AS customer
FROM
orders;
--查询json某个字段 作为键
select body -> 'key' as key from abc;
--查询json某个字段 作为文本(没引号)
select body ->> 'key' as key from abc;
SELECT name, body ->> 'key' key, body ->>'mtmNo' mtnNo from abc where body ->>
'key'='22'
pgsql:不区分utf8与utf8mb4支持表情符号：🖖
在第一次设置了id字段后想要在Navicat中修改为自增serial8是不能保存成功的，提示类型不存在。
解决办法：
删除原来字段重新新建字段设置为serial类型
新建表初始指定为serial类型
默认时间:
SELECT CURRENT_TIMESTAMP
启动生成UUID:

create extension "uuid-ossp"
SELECT uuid_generate_v4();

--查看索引

select * from pg_stat_user_indexes where relname = 'abc';

--分页

SELECT * from abc LIMIT 1 OFFSET 3;

CREATE TABLE "public"."abc" (2 "name" varchar COLLATE "pg_catalog"."default",
"id" int8 NOT NULL DEFAULT nextval('abc_id_seq'::regclass),
"c" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
"f" varchar(255) COLLATE "pg_catalog"."default" DEFAULT uuid_generate_v4(),
CONSTRAINT "abc_pkey" PRIMARY KEY ("id")
);
ALTER TABLE "public"."abc"
OWNER TO "postgres";

--查询body 中a=b
SELECT * from student where body@> '{"a":"b"}'::jsonb

 

常用函数

 

SELECT ('{ "postgres": { "release": 14 }}'::jsonb)['postgres']['release'];
SELECT (body::jsonb) ->>'key' key from abc;
--md5
SELECT md5('1234567');
--字符串拼接
SELECT 'a'||'b' a;
SELECT upper('a');
SELECT lower('A');
--base64
SELECT encode('hh', 'base64');
--解码
SELECT convert_from(decode('aGg=', 'base64'), 'utf8')
--uuid
SELECT uuid_generate_v4();

 

 //bigint 无法转化为Long 需要吧ID的属性改为BigInteger