PostGre使用总结
-- 建表
drop table if exists t_test;
create table t_test (
id SERIAL PRIMARY KEY -- 主键，自增，但删除数据后id会断层显示
, job_name text
, timest int
, width decimal
, create_time timestamp
)
;
-- 新增注释
comment on table t_test is '播报任务明细表';
comment on column t_test.id is '任务id';
comment on column t_test.job_name is '任务名称';
comment on column t_test.timest is '播报日期间隔的枚举值';
comment on column t_test.width is '字段宽度比例';
comment on column t_test.create_time is '任务创建者';
-- 主键 （如果建表语句里面没添加主键就执行该语句）
alter table public.user add constraint user_pkey primary key (id);
alter table public.user add primary key (id); -- 单一主键
alter table public.user add primary key (id, name); -- 复合主键
alter table public.user drop constraint table_name_pkey; -- 删除主键
-- 查询该表的主键名称
SELECT
pg_constraint.conname AS pk_name
FROM
pg_constraint
INNER JOIN pg_class ON pg_constraint.conrelid = pg_class.oid
WHERE
pg_class.relname = 'table_name'
AND pg_constraint.contype = 'p';
-- 索引或唯一索引
drop index if exists index_name;
create index index_name on t_test(id);
drop index if exists user_id_no;
create unique index user_id_no on user (id_no);
CREATE INDEX index_name -- 复合索引
ON t_user (id, name, city);
-- 查看表的索引名称
select * from pg_indexes where tablename = '';
-- 授权
grant all on table public.user to mydata;
grant select, update, insert, delete on table public.user to mydata_dml;
grant select on table public.user to mydata_qry;
-- 当插入pg表的数据，包含单引号时，需要使用 $$ 符号包裹内容。例如：
insert t_test (
sql_text
)
select
$$select * from t_test where dtstatdate >= '%s'$$ -- 这里有引号
from t_test
;
-- 模糊查看表名含有mesong关键字的表
select distinct table_name from information_schema.columns
where table_name like '%mesong%';
-- 换行符。反斜杠在PostGre中没有特殊含义，因此\n无法表示反斜杠的意思
select 'test line 1
test line 2' -- 换行的写法，方法一。直接在sql语句中换行
select 'test line 1' || E'\n' || 'test line 2'; -- 换行的写法，方法二。使用扩展常量
select 'test line 1' || chr(10) || 'test line 2'; -- 换行的写法，方法三。使用chr函数，推荐
-- 保留两位有效数字。例如，将0.108527946208801转化为10.85%
select cast(0.108527946208801*100 as decimal(18, 2)) || '%' -- 方法一
select round(cast(0.108527946208801*100 as numeric), 2) || '%' -- 方法二
-- 时间日期处理函数
select dtdate + interval '1 year'; -- 新增1年。注意，这里的dtdate一定需要是date类型，text类型不行
select dtdate + interval '1 month'; -- 新增1月
select dtdate + interval '1 week'; -- 新增1周
select dtdate + interval '1 day'; -- 新增1日
select extract(year from now()); -- 返回年份，例如2022
select extract(month from now()); -- 返回第几个月份，例如10，表示十月
select extract(week from now()); -- 返回第几周
select date('2021-01-01'); -- 将数据转换为date格式
select current_date -- date类型，返回类似"2022-07-08"
select to_char(current_date, 'yyyyMMdd') -- text类型，返回类似"20220708"
select to_char(current_date + interval '-10 day', 'yyyyMMdd') -- text类型，返回类似"20220628"，取10天前的文本类型日期
select date_trunc('week', current_date) -- date类型，返回类似"2022-07-04 00:00:00+08"。返回当周周一
-- 增删字段
alter table t_test add name text;
alter table t_test drop column if exists name;
-- 特性化函数
select string_agg(id, chr(10)) from t -- 将多行数据中的id合并到一个单元格，并加上分隔符chr(10)
-- 查询postgre表的大小
select pg_size_pretty(pg_table_size('demo')) as size;
-- dblink特性，在一个db上读取另外一个db的信息
select
city
, province
, district
from dblink(
'dbname=${db_name} host=${host} user=${host} password=${password}'::text
, 'select * from ${table_name}'::text
) t (city text, province text, district text)
limit 100
-- 查询视图创建的语句
select pg_get_viewdef('viewname', true)