postgre常用操作

postgre的数据组装结构

在 database 下有 scheme，在 scheme 下有 table。
如果需要做成 数据库，数据集，数据表 的形式，那么 scheme 是可以看成 数据集 的。

postgre可用技巧

使用左外连接提高查询效率

SQL

-- 这里就是先查一个表
-- 然后将另一个表和这个表关联的数据一起显示出来
-- 后边还用了一个条件判断，用于在不同的输入值下使用不同的判断条件
-- 这个 case 的含义是， inputValue 不为空，那么就使用 location = inputValue 的条件，如果为空，那么就返回所有数据，相当于不指定这个条件
select mft.name, mft.age from public.my_first_table mft
left outer join public.my_second_table mst
on mft.id = mst.id
where 
    case 
        when 'inputValue' != '' 
        then mft.location='inputValue' 
        else true 
    end
    and
    mft.title like '%男%';

json类型的查询方式

SQL

-- 在 postgresql 中
-- 使用 -> 返回的是 JSON类型 或 JSONB类型
-- 使用 ->> 返回的是 文本类型
-- 1. 直接查询某个键下的值
-- 使用的 json 数据如下
-- [
--     {
--         "data": {
--             "id": "hello"
--         }
--     }
-- ]
select e.json_data -> 0 -> 'data' ->> 'id' as json_id from public.easul_test e where id = 1;

-- 2. 使用 json_array_elements 将 json数组 转为一行一行的json值
-- jdata 字段的 `origin` 键的值是数组，使用 `json_array_elements` 可以将 数组 拆分成 行JSON值。
-- 并指定 `id` 查询每行中的id值
--
-- 查询 json数组 的值
select json_array_elements(jdata ->'origin')->'id' attr from  mylog  where usetime >= '2021-01-19' and code = 'logger' and requestType  = 'POST';
-- 使用 json数组 的值再查询 数组 中某些值
-- id 前边的两个箭头是为了输出为文本,可看参考三
select json_array_elements(myjdata) ->> 'id' from (select thedata -> 'data' myjdata from mylog where updatetime >= '2021-04-18' and "action" = 'PUT'
and code = 'test') as t1 ;

参考一
参考二
参考三

使用select测试函数

SQL

select substring(random()::varchar,3,32);

将时间转为字符串

SQL

select (CURRENT_TIMESTAMP :: text) like '%2023%'

常用的其他关键字

• group by: 将数据分组统计
• having: 用于过滤 group by 返回的分组
• order by: 按某个键排序
• distinct: 某个字段去重
• offset: 数据行数偏移
• limit: 限制取出的数据数量
• join: 多个表之间进行关联
• inner join: 返回两个表中匹配行的交集
• left join: 返回左表中的所有行，以及右表中的匹配行
• right join: 返回右表中的所有行，以及左表中的匹配行
• full join: 返回左表和右表中的所有行，以及同时匹配的行。
• like: 用于模糊匹配
• between: 用于选择在指定范围内的值
• in: 在某个范围内的值
• exists: 测试子查询是否返回任何行
  SQL

  -- exists 中的 子查询 如果返回了行
  -- 则结果为 true
  -- 否则结果为 false
  select 
      id 
  from 
      public."FH_XHGL_XHGJGL" fxx  
  where 
      exists(
          select 
              id 
          from 
              public."FH_XHGL_XHGJGL" 
          where id = 123123
      );

• asc: 倒序排列
• union: 进行两个 select 数据的合并
• is not null: 判断结果不为 null

postgre常识

• character varying 是 varchar 的全名

常用函数

统计数量

SQL

-- 统计所有行数
select count(1) from my_table;

类型转换

SQL

-- 使用 :: 符号进行转换
select '490138d7-bb37-4cdd-8e6f-0935181617ae'::uuid
-- 常用转换有
-- 转为uuid
select my_column::uuid
-- 转为json
select my_column::json

字符串转数组

SQL

-- 将包含 特定分隔符 的字符串转换为 数组
-- 会自动去除子串 前后空格
string_to_array('asdf1,asdf2,asdf3', ',')
-- 然后可以使用如下函数将以上 数组 转换为 行结构
unnest()
-- 最后嵌套结果为
unnest(string_to_array('asdf1,asdf2,asdf3', ','))
-- 可用的情况如下
update my_table set is_del=1 where id in  (select * from unnest(string_to_array('${id}',',')))

字段转数组

SQL

-- 该函数可以将表字段的值转为 数组
array_agg(user_id)
-- 因为是数组，所以可以用下标获取值
(array_agg(user_id))[1]
-- 使用如下函数可以将 数组 转为 字符串
array_to_string(array_agg(user_id),',')
-- 测试如下
select array_to_string(array_agg(user_id),',') from my_table;

字符串分割

SQL

-- 使用 , 分割第一个字符串，并获取最后结果数组中第一个结果
split_part('hello,world', ',', 1)
-- 可配合 trim 去除两边空格
trim(' hello ')
-- 测试
select trim(split_part('hello,world', ',', 1))

字符串合并

SQL

-- 进行字符串合并操作
select ('hello' || ((my_json::json)->0->>'id'))

设置默认值

SQL

-- 返回第一个非 null 值，
-- 可以用于设置默认值，保证不会返回异常数据
select COALESCE('', 'hello')

随机数

SQL

-- 生成 0 - 1 的浮点小数
random()
-- 如果想要截取小数部分，可以使用子串函数
-- 这会将 随机数 转为 字符串 ，然后从小数部分第一位取32位
substring(random()::varchar,3,32)
-- 测试
select substring(random()::varchar,3,32)

获取子串

SQL

-- 从 3 个位置 获取 32 个字符
substring('asdfasdfasdfasdfasfd',3,32)
-- 测试
select substring('asdfasdfasdfasdfasfd',3,32)

查询子串位置

SQL

-- 前边是子串，后边是主串
-- 存在则返回大于0的值
POSITION('our' IN 'w3resource')
-- 测试
select POSITION('our' IN 'w3resource') > 0
-- 也可以当做判断条件，判断某个字段中的数据是否有某个子数据
select first_name from my_table where POSITION('an' IN first_name) > 0

时间转换

SQL

-- 1. 转为时间戳
-- 将 2023-11-27 15:48:35 类型 转为 postgresql 中可用的时间戳类型
to_timestamp('2023-11-27 15:48:35','yyyy-MM-dd hh24:mi:ss')
-- 测试
select to_timestamp('2023-11-27 15:48:35','yyyy-MM-dd hh24:mi:ss');
-- 2. 转为日期
DATE(CURRENT_TIMESTAMP)
-- 测试
select DATE(CURRENT_TIMESTAMP)
-- 3. 将字符串转为日期
to_date('2023-11-27 15:48:35', 'yyyy-MM-dd')
-- 测试
select to_date('2023-11-27 15:48:35', 'yyyy-MM-dd')

将时间，数组转为字符串

SQL

-- 将 时间戳 转为 字符串
TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS');
-- 将 时间间隔 转为 字符串
TO_CHAR(INTERVAL '5 year 6 months 12 days', 'YYYY/MM/DD');
-- 将 日期 转为 字符串
TO_CHAR(CURRENT_DATE, 'MON DD, YYYY');
-- 将 整数值 转换为 字符串
-- 9 表示数字值， , 用于分割数字
TO_CHAR(572172, '999,999');
-- 将 numeric 转为 字符串
-- D 表示小数点的数字格式
TO_CHAR(572.172, '999D999');
--测试
SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS');

时间字段加减

SQL

-- 在 event_time 字段减去 9年
update public.my_table set event_time = event_time - interval '9 years'  where event_time >= '2032-01-01'

唯一ID生成

可以使用 雪花算法 生成（常用于分布式系统的全局唯一ID）
这个函数的作用是生成一个64位的整数，其中包含了时间戳、数据中心 ID 和机器 ID 等信息

SQL

select snow_next_id();

也可以使用UUID生成

将多条数据合并为json数组

SQL

-- 只用于一个字段
SELECT json_agg(id) as re
FROM public.result;

正则

SQL

-- 进行字符串替换，并使用 捕获组 中的某个值
-- 各个参数的含义为 列值 正则 替换的结果 替换的范围
-- \1 指的是用的是 捕获组1
select regexp_replace(my_column, '"id":"(.*?)"', '"url":"hello\1"', 'g')

开启UUID自动生成

SQL

-- 安装 uuid-ossp 扩展，用于开启UUID自动生成的功能
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- 然后就可以用于创建主键和插入默认值时使用
CREATE TABLE public.my_table (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4()
)
INSERT INTO public.my_table(
    id
) VALUES (
    uuid_generate_v4()
)

创建自增主键

SQL

-- serial: 会创建 integer 类型的自增主键。插入数据时不需要显式提供，postgre会自动生成
-- bigserial: 会创建 bigint 类型的自增主键。插入数据时不需要显式提供，postgre会自动生成
CREATE TABLE my_table (
  id serial PRIMARY KEY,
  name text
);

创建自增字段

SQL

-- 如果最开始没有创建自增主键
-- 后边需要加一个自增字段，可以使用如下方式
-- 为 表 添加一个 bigint 类型 的字段
ALTER TABLE table_1 ADD COLUMN serial_id bigint;
-- 为 表 创建一个新的 序列
CREATE SEQUENCE table_1_seq;
-- 使用以下 SQL 语句来为已有数据逐步添加 bigserial 的值
-- 避免一次更新所有数据造成性能问题
WITH id_list AS (
  SELECT ctid 
  FROM table_1
  WHERE serial_id IS NULL
  LIMIT 10000  -- 或者你希望的批量大小
) 
UPDATE table_1 
SET serial_id = nextval('table_1_seq') 
WHERE ctid = (SELECT rowid FROM id_list); -- 这里的值是一个一个比较的
-- 将新添加的列 serial_id 设置为默认值
ALTER TABLE table_1 ALTER COLUMN serial_id SET DEFAULT nextval('table_1_seq');

表字段增加

SQL

ALTER TABLE public.my_table
ADD COLUMN the_length float8 DEFAULT NULL,
ADD COLUMN my_name varchar(500);

表字段类型修改

SQL

ALTER TABLE public."SB_ZB" ALTER COLUMN gc TYPE int8 USING gc::int8;

表外键添加

SQL

ALTER TABLE my_table ADD CONSTRAINT my_table_fk FOREIGN KEY (first_id) REFERENCES public.main_table(main_id)

创建临时命名查询

SQL

-- 可以将查询出来的结果组成一个变量，从而方便多个语句使用
WITH id_list AS (
  SELECT ctid 
  FROM table_1
  WHERE serial_id IS NULL
  LIMIT 10000  -- 或者你希望的批量大小
) 

创建表时的必备字段

SQL

-- id
-- 创建时间: 这里使用的是unix时间，抽取的是秒数。也可以使用timestamp
-- 创建人
-- 更新时间: 这里使用的是unix时间，抽取的是秒数。也可以使用timestamp
-- 更新人
-- 是否逻辑删除
CREATE TABLE public.my_table (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    create_time bigint DEFAULT extract(epoch from current_timestamp),
    create_user varchar(50) DEFAULT NULL,
    update_time bigint DEFAULT extract(epoch from current_timestamp),
    update_user varchar(50) DEFAULT NULL,
    is_logical_del boolean DEFAULT false
)

创建表时添加字段注释

创建表的相关SQL可以查看(这里)[#创建表时的必备字段]。
如果需要在创建表的过程中，顺便加上字段注释，需要使用如下SQL。

SQL

-- 使用 COMMENT 语句为每一个字段添加注释
COMMENT ON COLUMN public.my_table.id IS '主键';

创建表时添加表注释

SQL

-- 在表上添加注释，只是将关键字换为 TABLE
-- 然后再加上表名即可
COMMENT ON TABLE public.my_table IS 'This is the table users';

创建索引

SQL

-- 在 SB_ZB 表上创建索引 idx_SB_ZB_geometry
-- 索引使用 gist 类型，并应用到 geometry 列
CREATE INDEX "idx_SB_ZB_geometry" ON public."SB_ZB" USING gist (geometry);

几个时间的默认值

SQL

-- CURRENT_TIMESTAMP: 用于填充默认的 timestamp 类型的值，值的样式为 2023-11-17 00:00:00.000 
-- CURRENT_DATE: 用于填充默认的 date 类型的值，值的样式为 2023-11-17
CREATE TABLE my_table (
  id serial PRIMARY KEY,
  my_timestamp timestamp DEFAULT CURRENT_TIMESTAMP,
  my_date date DEFAULT CURRENT_DATE
);

插入数据时根据值不同插入不同的数据

SQL

INSERT INTO public.my_table
(fc_name, fc_type)
VALUES(
    case when 'asdf' != '' then NULL else 'asdf' end, 
    'asdfasdf'
);

查询数据时根据值不同使用不同条件

SQL

-- 这里在 where 中的 case 语句使用了判断
-- 这个 case 的含义是， inputValue 不为空，那么就使用 location = inputValue 的条件，如果为空，那么就返回所有数据，相当于不指定这个条件
select mft.name, mft.age from public.my_first_table mft
left outer join public.my_second_table mst
on mft.id = mst.id
where 
    case 
        when 'inputValue' != '' 
        then mft.location='inputValue' 
        else true 
    end
    and
    mft.title like '%男%';

分页查询

SQL

-- 使用 LIMIT 和 OFFSET 关键字
-- 需要传入 pageSize(页面大小) pageNo(查询第几页)
-- 下边使用 ${var} 来代表传入的变量
-- 这里是将游标移动到 (${pageNo} - 1) * ${pageSize} 的位置
-- 然后查询 ${pageSize} 条数据
SELECT
    name,
    value
FROM 
    my_table
WHERE 
    id > 15 
LIMIT
    ${pageSize}
OFFSET 
    (${pageNo} - 1) * ${pageSize}
;

更新操作

更新时根据条件更新

SQL

-- 更新时 ${$.http.reportStatus} 为 空字符串 则用原值，否则用该值
UPDATE public.my_table
SET 
    age=CASE
        WHEN '${$.http.reportStatus}' != '' THEN '${$.http.reportStatus}'
        ELSE age
    END
WHERE id='test';

更新时关联多个表

SQL

-- 1. 使用 FROM 子句关联一个表
UPDATE 
    public.table1
SET
    my_name = split_part(my_name, 'my_tag', 2) 
FROM
    public.table2
WHERE 
    table1.id = table2.id 
AND
    table2.a_type = 1
-- 2. 使用 FROM 子句关联多个表
UPDATE 
    table1
SET 
    table1.column1 = table2.column2,
    table1.column3 = table3.column4
FROM 
    table2
LEFT JOIN 
    table3 
ON 
    table1.id = table3.id
WHERE 
    table1.id = table2.id;

跨库操作

跨库创建视图

BASH

# 1. 安装必备扩展包
# 在如下目录 ls 的情况下，如果有 unpackaged 相关的文件则为未安装
# 然后使用包管理器进行安装即可
cd /usr/pgsql-12/share/extension
ls -al
yum install postgresql12-contrib

接下来的操作在 SQL 中进行

SQL

-- 2. 开启 dblink 相关扩展的功能
create extension if not exists dblink;
-- 3. 执行跨库视图创建
-- 这个 sql 是创建了一个视图 v_sys_data_dict
-- 数据从 dblink 这个函数 select 出来
-- 然后将相应字段映射到视图 v_sys_data_dict
-- 并指定不同的字段的类型
CREATE OR REPLACE VIEW public.v_sys_data_dict
AS SELECT v_sys_data_dict.id_,
    v_sys_data_dict.parent_id_
   FROM dblink('host=192.168.1.1 port=5432 dbname=MyDatabase user=postgres password=postgres'::text, 'select id_,parent_id_ from sys_data_dict'::text) v_sys_data_dict(id_ character varying(64), parent_id_ character varying(64));

跨域查询

先从 跨库创建视图 中执行 1 和 2
然后执行如下 SQL

SQL

select * from dblink('host=192.168.1.1 port=5432 dbname=MyDatabase user=postgres password=postgres'::text, 'select id_,parent_id_ from sys_data_dict'::text) as v_sys_data_dict(id_ character varying(64), parent_id_ character varying(64));

触发器操作

创建触发器

SQL

-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_modified_at()
    RETURNS TRIGGER AS $$
BEGIN
    -- 如果服务器的时区不对，可以使用如下 SQL 设置数据库时区为中国
	SET TIME ZONE 'Asia/Shanghai';
    -- 这个语句是用于修改即将执行的SQL中的字段
    -- 而不是在SQL执行完之后，再操作SQL已经影响过的那行数据
    NEW.modified_at = current_timestamp;
    -- 触发器函数可以放多条数据库语句
    NEW.created_at = current_timestamp;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建 INSERT 触发器
CREATE TRIGGER insert_trigger
    AFTER INSERT ON your_table
    FOR EACH ROW
    EXECUTE FUNCTION update_modified_at();

-- 创建 UPDATE 触发器
CREATE TRIGGER update_trigger
    AFTER UPDATE ON your_table
    FOR EACH ROW
    EXECUTE FUNCTION update_modified_at();

删除触发器

SQL

-- 删除触发器
DROP TRIGGER IF EXISTS your_trigger_name ON your_table_name;

-- 要先把所有的触发器都删除掉，
-- 再删除触发器函数
-- 触发器函数是有括号的，不要丢失
DROP FUNCTION IF EXISTS your_trigger_function_name();

修改触发器

SQL

-- 触发器修改需要先删除再创建
DROP TRIGGER your_trigger_name ON your_table_name;
CREATE TRIGGER your_trigger_name
BEFORE INSERT ON your_table_name
FOR EACH ROW
EXECUTE FUNCTION your_new_trigger_function();
-- 触发器函数修改可以直接如下
CREATE OR REPLACE FUNCTION your_trigger_function() RETURNS TRIGGER AS 
$$
BEGIN
    -- Your new function code here
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

系统信息查询

查询表与列注释

SQL

SELECT 
    c.relname AS table_name,
    obj_description(c.oid) AS table_comment,
    a.attname AS column_name,
    col_description(c.oid, a.attnum) AS column_comment
FROM 
    pg_class c
LEFT JOIN 
    pg_attribute a ON a.attrelid = c.oid
LEFT JOIN 
    pg_namespace n ON n.oid = c.relnamespace
WHERE 
    c.relkind = 'r' -- 只选择普通表
AND 
    n.nspname = 'public' -- 指定模式名，如果是默认模式可以设置为 'public'
AND 
    c.relname = 'zy_patrol_person' -- 替换为你的表名
ORDER BY 
    a.attnum; -- 按字段序号排序

查询数据库连接数相关信息

SQL

-- 当前连接数
SELECT count(*) AS current_connections
FROM pg_stat_activity;

-- 总连接数
SELECT setting AS max_connections
FROM pg_settings
WHERE name = 'max_connections';

-- 空闲连接数
SELECT count(*) AS idle_connections
FROM pg_stat_activity
WHERE state = 'idle';