PostgreSQL
文章平均质量分 65
梦想画家
开源软件爱好者,乐于博客分享,业余时间喜欢跑步和科幻,希望与意趣相投朋友一起学习交流。
展开
-
如何创建PostgreSQL 生成列
PostgreSQL中生成列是从其他列计算而来的特殊列。生成列与普通列不同,不是固定的值,而是又引用表中其他列的表达式决定的。生成列在SQL标准(ISO/IEC 9075)中引入,被主流RDBMS支持,PostgreSQL12开始支持生成列。原创 2023-01-11 09:43:38 · 879 阅读 · 0 评论 -
PostgreSQL事件触发器实战教程
PostgreSQL事件触发器实战教程本文带你学习PostgreSQL事件触发器,同时也介绍其他解决的解决方案,如日志DDL,跟踪提交时间戳。通过对比学习可以更好理解事件触发器的优势。PostgreSQL 提供了非常有用的事件触发器,用于检测数据变化并自动执行后续任务。例如,触发器可用于检测一个表的变化,然后在另一个表上执行相关任务,通常使用这种方式实现集中审计表,其中包括所有表的变化信息。在9.3版本之前DDL变化不能被可靠检测,9.3版本引入事件触发器让其变为可能。在介绍事件触发其之前,我们先原创 2021-11-27 11:04:45 · 2427 阅读 · 1 评论 -
PostgreSQL 配置文件 postgresql.conf 及 postgresql.auto.conf
本文我们学习 PostgreSQL 配置文件 ,了解配置文件为什么要有两个、如何修改配置等内容以及修改配置文件缺省位置。两个配置文件我们对 postgresql.conf 不陌生,但PostgreSQL在 9.4及以上版本还提供了 postgresql.auto.conf 配置文件。那么为什么要两个配置文件呢?当我们打开 postgresql.conf 查看内容,会看到非常多的参数。其他大多数被注释了,意味着自动被赋予缺省值。我们可以去掉注释编辑特定值,但这样比较难以跟踪变化。为此,Postgr.原创 2021-10-01 21:23:49 · 14935 阅读 · 0 评论 -
PostgreSQL 权限管理教程
本文介绍PostgreSQL 权限管理,使用GRANT 授权,REVOKE撤销权限。授权使用 login 属性创建角色之后,角色可以登录数据库,但不能对数据库对象做任何事情,如表、视图、函数等。为了让角色与数据库对象进行交互,需要数据库对象权限给角色。下面示例给角色授权:GRANT privilege_list | ALL ON table_nameTO role_name;privilege_list 包括 SELECT, INSERT, UPDATE, DELETE, TRUNC.原创 2021-09-28 08:08:09 · 3710 阅读 · 0 评论 -
PostgreSQL表标识列教程
本文学习使用 GENERATED AS IDENTITY 约束创建 PostgreSQL 表的标识列 。标识列介绍PostgreSQL 10 引入新的特性:GENERATED AS IDENTITY 约束,可以给列自动赋唯一值,它是非常好用serial 列的变体。语法如下:column_name type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY[ ( sequence_option )type 可以是 SMALLINT, INT, or B.原创 2021-09-13 19:40:05 · 3564 阅读 · 0 评论 -
PostgreSQL 生成列(Generated Columns)教程
PostgreSQL 12 增加新的特性——生成列(Generated Columns),也就是计算列。在之前版本也可以实现,但需要定义函数和触发器,利用该功能可以更容易使用并可以提升性能。生成列是给表指定计算列,其数据可以根据其他列数据自动生成,当原数据更新时其自动更新。PostgreSQL 12+ 生成列介绍在PostgreSQL 12+中生成列作为内置功能,可以在创建或修改表时指定列作为生成列,指定其内容通过表达式自动填充,可以为简单基于其他进行数学运算,或更复杂的函数。其优势包括:无需.原创 2021-09-13 19:39:02 · 2876 阅读 · 0 评论 -
PostgreSQL 完全外连接(FULL OUTER JOIN) 教程
本文学习如何使用PostgreSQL 完全外连接,从两个或多个表中查询数据。完全外连接介绍完全外连接合并左连接和右连接的结果。对于连接表不匹配的行,完全连接设置每一列的值为null 。如果一个表的行与另一个表匹配,那么结果将包含来自两个的相应列。假设需要从A,B两个完全连接查询结果,语法如下:SELECT * FROM AFULL [OUTER] JOIN B on A.id = B.id;其中 [OUTER] 关键字为可选的。下图使用韦恩图表示完全连接:图1其结果包括两个表匹配的.原创 2021-09-13 17:15:21 · 6417 阅读 · 0 评论 -
PostgreSQL 常用字符串分割函数
1. SPLIT_PARTSPLIT_PART() 函数通过指定分隔符分割字符串,并返回第N个子串。语法:SPLIT_PART(string, delimiter, position)string : 待分割的字符串delimiter:指定分割字符串position:返回第几个字串,从1开始,该参数必须是正数。如果参数值大于分割后字符串的数量,函数返回空串。示例:SELECT SPLIT_PART('A,B,C', ',', 2); -- 返回B下面我们利用该函数分割日期,获取年月原创 2021-09-11 21:25:51 · 26671 阅读 · 1 评论 -
PostgreSQL Update join 和 Delete join 教程
本文学习如何使用 Update join 和 Delete join 语法,实现基于另一张表更新和删除表数据。Update join语法介绍Update join用于基于另一张表更新表数据,语法如下:UPDATE t1SET t1.c1 = new_valueFROM t2WHERE t1.c2 = t2.c2;from 关键字后面指定另一张表,where指定关联条件。对于t1表中每一行,update语句检查表t2,如果t1.c2 与 t2.c2 相等,则更新t1.c1为新的值。示例.原创 2021-09-02 20:11:35 · 11225 阅读 · 1 评论 -
PostgreSql索引维护教程
上文我们简要介绍了索引,本文补充介绍一些关于索引的维护知识。索引维护查看索引大小有两种方式查看关系大小,关系可以时表或索引。显示表大小,不包括索引:select pg_size_pretty(pg_relation_size('film'));-- 432 kB查看表大小包括索引:select pg_size_pretty(pg_total_relation_size('film'));-- 688 kB同样可以单独查看索引大小:select pg_size_pretty(pg_.原创 2021-08-31 21:02:56 · 521 阅读 · 0 评论 -
PostgreSQL 物化视图教程2
上节我们简要介绍了物化视图,本文补充说明创建和修改物化视图的一些选项。创建物化视图在了解物化视图之前,我们首先导入一些示例数据作为计算的基础:CREATE TABLE t_demo (grp int, data numeric);INSERT INTO t_demo SELECT 1, random() FROM generate_series(1, 5000000);INSERT INTO t_demo SELECT 2, random() FROM generate_serie.原创 2021-08-31 15:50:16 · 1219 阅读 · 0 评论 -
列顺序影响PostgreSQL表存储空间
当定义表结构时,通常不会关心表字段顺序,因为select后面列顺序并不影响结果。但列顺序会影响表实际存储大小,也就说即使数据完全一样,但列顺序不同存储空间不同。因为CPU对齐的缘故(cpu按照块方式访问),从而影响数据结构的内部大小。可能你不会相信,下面我们测试。字段顺序布局test=# CREATE TABLE t_test ( i1 int, i2 int, i3 int, v1 varchar(100),原创 2021-08-30 21:19:19 · 328 阅读 · 0 评论 -
使用 PostgreSQL CTEs 实现递归查询
本文介绍PostgreSQL 递归查询,首先介绍其语法结构,然后通过几个示例进行说明。语法说明严格意义上说迭代过程而不是递归,但 RECURSIVE 是SQL标准委员会选择的术语。一般PostgreSQL 递归查询结构包括:非递归部分select语句Union or Union all递归部分select语句语法如下:WITH RECURSIVE name_cte AS (SELECT statement /* non-recursive statement */UNION [A.原创 2021-08-29 22:43:29 · 1763 阅读 · 0 评论 -
备份 PostgreSQL特定schema或table数据
除了创建独立数据库,PostgreSQL DBA 通常建议创建schema,因为PostgreSQL不支持跨库进行查询。在当前数据库中,你不能选择当前数据库服务器上的任何其他数据库的数据,如果要实现需要配置DB link。因此大多数数据库用户会为不同应用场景创建不同的schema,本文针对这种应用场景介绍如何备份特定schema及表。备份特定范围数据备份schemapg_dump -U postgres -d postgres --schema=public > back1.sql备份指定原创 2021-08-24 12:23:39 · 5175 阅读 · 0 评论 -
查看PostgreSQL 表结构及权限列表
本文我们解释如何查看PostgreSQL 表结构以及权限列表,即哪些用户对表拥有哪些权限。查看表结构使用命令查看\d+ table_name;示例:test=# \d+ books 数据表 "public.books" 栏位 | 类型 | Collation | Nullable | Default | 存储 | 统计目标 | 描述--.原创 2021-08-13 10:14:41 · 8540 阅读 · 0 评论 -
PostgreSQL组合唯一约束空值问题
因为 PostgreSQL唯一约束并考虑空值的唯一性,我们虽然在列上定义了唯一约束,但仍然会存在重复数据。PostgreSQL唯一约束的规则是,唯一键的列值可以为NULL。问题描述在多个列上定义组合唯一键,那么当其中一个值为空而其他值不为空时约束不起作用,下面看详细过程。创建表CREATE TABLE TestUniqueNull( ID INTEGER ,NoA INTEGER ,NoB INTEGER ,NoC INTEGER ,CONS.原创 2021-08-12 17:38:20 · 3272 阅读 · 0 评论 -
PostgreSQL 删除重复数据
本文介绍几种方法删除PostgreSQL表中的重复数据记录。准备数据创建 basket 表:CREATE TABLE basket( id SERIAL PRIMARY KEY, fruit VARCHAR(50) NOT NULL);插入示例数据:INSERT INTO basket(fruit) values('apple');INSERT INTO basket(fruit) values('apple');INSERT INTO basket(fruit) v.原创 2021-08-10 12:06:53 · 8636 阅读 · 1 评论 -
PostgreSQL 物化视图快速入门
本文介绍PostgreSQL 物化视图 (materialized views) ,物化视图用于存储物理的查询结果并可周期性更新。视图是底层物理表的虚拟表,其本身不保存数据。为了区别我们称为简单视图,postgres 9.5 版本之后默认也可以通过视图更新底层表数据。物化视图扩展简单视图概念,允许存储物理数据。物化视图缓存复杂查询结果并可以周期性刷新结果。主要用于需要快速访问数据的场景,如数据仓库和商务智能应用中。物化视图介绍创建物化视图使用 CREATE MATERIALIZED VIEW .原创 2021-08-07 16:28:31 · 4223 阅读 · 0 评论 -
PostgreSql 索引简明教程
PostgreSql 索引简明教程索引是数据库引擎加速获取数据的查找表。简言之,索引是指向表数据的指针,类似书的目录。PostgreSql 提供了Btree、Hash、GiST、SP-GiST、GIN、BRIN等多种索引类型,每种索引类型使用不同的算法来适应不同类型的查询。在默认情况下,创建的索引类型为B-tree索引。1. 索引概述索引可以加速查询,但会降低插入或更新速度。创建或删除索引不会影响数据。创建索引创建索引语法:CREATE INDEX index_name ON table_原创 2021-07-29 10:18:32 · 2298 阅读 · 0 评论 -
PostgreSQL 从 csv 文件导入数据
本文我们学习如何把csv数据文件导入至PostgreSQL中。创建实例表和数据首先创建persons表,包括五个字段:idfirst_namelast_namedob : 出生日期emailCREATE TABLE persons ( id SERIAL, first_name VARCHAR(50), last_name VARCHAR(50), dob DATE, email VARCHAR(255), PRIMARY KEY (id))准备csv数.原创 2021-07-27 13:08:38 · 11064 阅读 · 0 评论 -
PostgreSQL 实战——查询上月状态没有成功的记录
本文通过示例进行实战学习,如何有效查找上月状态没有成功的记录。需求说明假设有过程在后台运行并在数据库中记录状态。现在需要查询上月状态从未成功的记录。insert into exp_table values(1 ,'2021-06-15' ,'FAILED'), -- <- PID 1 was successful, but in last month (1 ,'2021-06-05' ,'FAILED'),(1 ,'2021-06-01' ,'FAILED'),(1 .原创 2021-07-27 12:54:14 · 274 阅读 · 0 评论 -
PostgreSQL COALESCE 和 NULLIF 函数
本文学习PostgreSQL COALESCE 和 NULLIF函数。COALESCE它返回第一个非空参数,并通过示例让你了解如何在select语句中有效处理空值。NULLIF需要两个参数,参数相同返回null,否则返回第一个参数。通过同时学习两个函数,可以区分两者,同时可以组合使用,增强你SQL的健壮性。PostgreSQL COALESCE 函数语法 COALESCE 函数语法:COALESCE (argument_1, argument_2, …); 参数可以有无限个,总是返回第一个.原创 2021-07-26 22:27:30 · 26230 阅读 · 1 评论 -
PostgreSQL 利用 array_agg 生成交叉表记录
前文我们通过多种方法创建交叉表,但有时前端语言需要容易处理 json 形式记录,然后通过适当控件渲染交叉表或图表。本文还是通过前文的数据进行讲解,示例数据可以通过前文获取,这里就直接开始了。查看每个学生每月的各科测评情况 select stu_name, extract (month from eval_day) eval_month, subject, max(eval_result) eval_result from evaluations group by 1, 2, 3返回结果:.原创 2021-07-22 21:45:47 · 702 阅读 · 0 评论 -
PostgreSQL 使用简单 case 实现交叉表
前文我们介绍了如何通过 crosstab 扩展实现交叉表,本文回归原始基本的SQL实现方式,利用case语句 实现交叉表,读者可以对比两者之间差异,增强SQL分析实现能力。准备示例数据为了演示方便,创建关于浏览web页面的记录表,包括三个字段:日期、操作系统、以及访问次数。下面填充一些随机数据:create table daily_browse as select on_date::date, b.desc AS TYPE, (random() .原创 2021-07-17 21:46:17 · 571 阅读 · 1 评论 -
PostgreSQL NUMERIC 数据类型
NUMERIC 类型能存储大数据量的数值。通常NUMERIC类型用于需要精确性的数字,如货币数量或度量。本文介绍 NUMERIC ,并通过示例学习它的特性。介绍 NUMERICNUMERIC类型的语法:NUMERIC(precision, scale)precision 表示整个数据长度,scale 表示小数部分的长度。如: 1234.567 ,precision 为 7 ,scale 为 3.NUMERIC 类型 在小数点前面长度可达到 131,072 ,小数点后面长度可达到 16,38.原创 2021-07-17 17:12:22 · 38767 阅读 · 1 评论 -
PostgreSQL JSONB类型及其操作
PostgreSQL 9.2 版本引入Json作为基本类型,它可以存储json对象,使得PostgreSQL 支持无Schema的NoSQL特性。PostgreSQL 9.4 有加入了jsonb 类型,本文带你了解jsonb类型,并通过示例学习相关操作。JSONB类型json类型以文本方式存储json对象,所以每次处理时需要解析和分析文本格式的json。另外还存储了不必要的空白字符和重复键。jsonb类型转换文本格式json对象未二进制格式,并删除了不需要的白色空格及重复键。因为有了预处理,jso.原创 2021-07-13 21:38:57 · 9613 阅读 · 1 评论 -
PostgreSQL 实现交叉表查询(2)
上节我们学习使用 tablefunc实现交叉表查询。但还不够强大,能不能展示每月的平均成绩或总成绩,或同时显示平均成绩或总成绩。本文提供两种方法进行说明:组合 使用tablefunc,还有原始的 case when 方式进行实现。示例数据我们示例表是学生成绩表,包括学生姓名、科目、成绩、考试日期:create table evaluations( stu_name varchar(60), subject varchar(60), eval_result numeric(3,1), eval_原创 2021-07-08 23:06:47 · 768 阅读 · 0 评论 -
PostgreSQL Array 教程
本文带你学习PostgreSQL Array 类型,包括如何操作数组类型以及一些常用函数。Array类型数组类型是 Postgresql 的重要角色之一。每种数据都有其伴随的数组类型,如integer 对应 integer[] ,character 对应 character[] 。针对自定义数据类型,PostgreSQL在后台也创建了对应的伴随数组类型。PostgreSQL 支持任何有效的数据类型列为数组类型,包括内置类型、用户定义类型、枚举类型。下面示例,定义contacts 表,phone .原创 2021-07-04 12:00:03 · 7088 阅读 · 1 评论 -
`PostgreSQL` 实现交叉表查询(1)
自 PostgreSQL 8.3 版本开始,引入了 tablefunc扩展功能。它提供了很多有趣功能,其中就包括 tablefunc 函数,可以创建交叉表功能。学习最佳方式就是从示例开始,我们首先初始化表和数据,然后创建交叉表。本文先实现简单交叉表,后续在增加更复杂功能。创建示例表和数据我们示例表是学生成绩表,包括学生姓名、科目、成绩、考试日期:create table evaluations( stu_name varchar(60), subject varchar(60), eval.原创 2021-07-03 22:07:57 · 892 阅读 · 2 评论 -
PostgreSQL检测时间序列活动周期
本文通过示例学习如何发现时间序列的活动周期,如:用户什么时间段处于活动状态或有活动数据。本文给一些思路,希望对你有帮助。1. 准备数据为了简化,这里简单生成一些示例数据,代码如下:CREATE TABLE t_series (t date, data int); COPY t_series FROM stdin DELIMITER ';';2018-03-01;122018-03-02;432018-03-03;92018-03-04;132018-03-09;232018-03-.原创 2021-05-13 22:01:57 · 299 阅读 · 0 评论 -
PostgreSQL 9.4 引入特性:WITHIN GROUP 和 FILTER 子句
PostgreSQL 9.4扩展SQL标准,增加了两个新的子句:WITHIN GROUP 和 FILTER 。WITHIN GROUP 子句WITHIN GROUP 对有序子集执行聚集函数非常有用。PostgreSQL9.0 版本引入窗口函数用于关联数据子集和其对应每个记录,针对任何特定记录定义一组聚集,利用 OVER(PARTITION BY/ORDER BY) 子句分组执行聚集函数。使用PostgreSQL 9.4 版本的 WITHIN GROUP 子句可以简化之前使用窗口函数聚集有序子集数据.原创 2021-05-13 20:23:48 · 3448 阅读 · 1 评论 -
PostgreSQL中计算百分位数和中位数
PostgreSQL9.4版本之后很容易对一组值计算百分位数,主要是用有序集合的聚集函数percentile_cont 和 percentile_disc。这两个函数类似,但对合并结果有些差异:percentile_disc 返回最接近请求百分位的离散值。percentile_cont 返回基于分布的多个值的连续值(插值)。更精确,包括两个输入值直接的数(带小数)。下面通过示例进行讲解,首先我们准备一个示例表和数据。create table thing ( value int);ins原创 2021-05-11 21:38:40 · 10134 阅读 · 0 评论 -
PostgreSQL PERCENT_RANK 和 CUMM_DIST 函数比较
PostgreSQL 提供了一些系列窗口函数。其中有些非常相似,但也有细微差别。本文主要讨论PERCENT_RANK 和 CUMM_DIST 两个函数。两者都是返回当前行的相对位置,值在0~1之间;其中PERCENT_RANK的计算公示为: (rank-1) / (total rows – 1);CUMM_DIST对应公示为:(rank) / (total rows) 。一般情况说明首先,cume_dist计算“小于或等于”的行的百分比,而percent_rank计算“小于”当前行的类似百分比,只不过它原创 2021-04-28 20:58:28 · 926 阅读 · 0 评论 -
PostgreSQL 日期时间类型
1. 日期类型本节先介绍几种最常见的日期类型。Date类型表示日期,表示范围4713 BC ~ 5874897 AD,精确至天。当前日期current_date,另外还有几个关键词:test=# select current_date::date, 'yesterday'::date,'today'::date,'tomorrow'::date; current_date | date | date | date--------------+------------原创 2021-04-28 20:22:16 · 10407 阅读 · 0 评论 -
PostgreSQL时间序列分析示例——模式匹配
本文通过实例讲解简单时间序列模式分析,如查找连续三个月增长的记录。示例数据为了演示,创建简单示例表,包括少部分示例数据:CREATE TABLE t_timeseries( id serial, data numeric);COPY t_timeseries FROM stdin DELIMITER ',';1,112,143,164,95,126,137,148,99,1510,9\.现原创 2021-04-27 21:51:21 · 671 阅读 · 0 评论 -
PostgreSQL时间序列分析入门
本文通过简单示例介绍时间序列分析,如求增量与相关性。示例数据为了演示,创建简单表,加载一些示例数据。可参考上文内容。test=# CREATE TABLE t_oil ( region text, country text, year int, production int, consumption int);加载数据命令:COPY t_oil FROM PROGRAM 'curl https:原创 2021-04-25 22:03:12 · 795 阅读 · 0 评论 -
Postgresql分组数据分析
熟悉SQL的读者对Group by 和 having应该不陌生。但可能不熟悉 cube, rollup, grouping sets 。本文带你学习并比较它们的差异,内容参考《mastering postgresql 10》.1. 示例数据在正式开始之前,我们先创建示例表,并copy一些示例数据。create table t_oil( region text, country text, year int , production int , consumption int);利用c原创 2021-04-24 21:35:21 · 1164 阅读 · 0 评论 -
使用pg_stat_activity视图监控Postgresql
当监控或检查Postgresql时,我通常先查看一些系统视图,然后再进行深入分析。本文带你了解pg_stat_activity视图,通过监控信息掌握系统慢查询问题。pg_stat_activity视图首先要讨论的是pg_stat_activity,其目的是让你掌握系统此刻正在做什么。postgres=# \d pg_stat_activity; 视图 "pg_catalog.pg_stat_activity" 栏位 |原创 2021-04-14 20:33:24 · 1372 阅读 · 0 评论 -
PostgreSql DDL事务
PostgreSql的DDL事务是一个非常好的特性,即在一个事务块中运行DDL语句(改变数据结构的命令)。甚至很多商业数据库也不具有,一个DDL语句会隐式提交当前事务。示例除了很少一部分DDL语句,如:DROP DATABASE,CREATE TABLESPACE/DROP TABLESPACE等。其他PostgreSql的DDL可以在事务块内,这是巨大的加分项,让用户从中获益。请看示例:postgres=# begin;BEGINpostgres=# create table t_test(i原创 2021-04-10 21:50:44 · 1449 阅读 · 0 评论 -
PostgreSQL通过pg_trgm扩展的三元索引实现全文检索
本文介绍利用pg_trgm扩展实现全文检索。1. 概述PostgreSQL可以使用三元索引增强全文检索。三元算法对字符串进行分词,如“dog” 被分为 “d”,”do”,”dog”,”og”多个词。pg_trgm扩展支持三元匹配。也在Gist和Gin索引上使用三元匹配操作提升检索速度或性能。对于全文搜索提供了不同的同义词,同时也可以使用Trigram来实现打字更正和建议。下面通过示例说明。2. 模糊匹配这里先介绍postgresql几种模糊操作符:like都比较熟悉的大小写敏感匹配,ili原创 2021-03-05 22:23:04 · 2272 阅读 · 1 评论