PGSQL使用总结

-------------------------------------------------------------------------------------------------------------------

colume_name:字段名，table_name:表名

-------------------------------------------------------------------------------------------------------------------

一.常用大小写转换操作

1.转换为大写

UPPER()

SELECT UPPER(column_name) FROM table_name;

2.转换为小写

LOWER()

SELECT LOWER(column_name) FROM table_name;

3.将首字母大写

INITCAP()

SELECT INITCAP(column_name) FROM table_name;

4.修改表中字段大小写

-- 转大写
UPDATE table_name SET column_name = UPPER(column_name);
-- 转小写
UPDATE table_name SET column_name = LOWER(column_name);
-- 修改首字母大写
UPDATE table_name SET colume_name = INITCAP(column_name);

二.其他操作

1.生成随机数

CREATE OR REPLACE FUNCTION generate_unique_random_number(OUT random_number bigint) AS
$$
BEGIN
    -- 生成随机数
    random_number := floor(random() * power(10, 18));
END;
$$
    LANGUAGE plpgsql;
-- 查询
SELECT generate_unique_random_number();

2.查询库中表主键

SELECT att.attname, cls.relname
FROM pg_index idx
         JOIN pg_class cls ON idx.indrelid = cls.oid
         JOIN pg_attribute att ON cls.oid = att.attrelid AND att.attnum = ANY (idx.indkey)
WHERE idx.indisunique = true
  AND cls.relname in (
    'table_name');