PostGre使用总结

引言

PostGre数据库是常用的RDBMS，这里对其用法进行总结。

常用语法

引用自 https://blog.csdn.net/meser88/article/details/98170744

-- 建表
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)

---

安装

其实现在使用各类db，一般直接申请云上设施的SASS服务即可，现在不再建议在本机手动安装Postgre了。这里仅做记录罢了。

Windows

安装之后会创建默认数据库，库名为 postgres
默认账号为 postgres，密码为 root

Mac

安装参考链接，比较简单。Mac下的Navicat破解比较麻烦，因此推荐直接使用自带的 pgAdmin4，基本的功能都能满足，开箱即用。

Linux(CentOS)

总的来说，在Linux安装Postgre比较麻烦。如果只是为了开发测试，建议安装在本机即可。

# 查看Linux版本
uname -a

# 在CentOS安装PostGre
yum install postgresql postgresql-server

# 用postgres用户初始化数据库
su - postgres

# 初始化PostgreSQL
initdb

# 离开
exit

# 添加PostgreSQL自启动菜单
systemctl enable postgresql

# 启动PostgreSQL
systemctl start postgresql

# 切换用户。postgres 用户是默认自动创建的
su - postgres

# 进去Postgre SQL Shell
psql

# 显示当前用户
select * from current_usert;

# 显示所有用户
\du

# 在psql中进行下列操作
postgres# CREATE USER root WITH PASSWORD 'root';
postgres# CREATE DATABASE root;
postgres# GRANT ALL PRIVILEGES ON DATABASE root to root;
postgres# ALTER USER postgres WITH PASSWORD 'root';

# 退出
\q

# 下次这样登陆
psql -d root -h localhost -p 5432 -U root -W root

查看视图创建语句

在PgAdmin3客户端查看视图的创建语句