分类：postgresql

2021
02-21

Postgresql 数据库权限功能的使用总结

Postgresql数据库支持灵活的权限管理，可以控制一个角色（组、用户）对某张表的读、写、更新、删除等操作权限、执行某个函数的权限以及操作（使用、更新等）视图、序列的权限。PG的权限管理功能比较强大，可以细化到对一张表的各个字段，比如禁止用户访问一张表里的密码字段等，在稍后的内容中给出详细的解释。虽然在PG数据库中把用户、角色统一叫做角色，甚至创建语句都为createroleXXX,但用户和角色之间仍有一定的区别。在这里... 继续阅读 >

2021
02-21

postgresql 删除重复数据的几种方法小结

在使用PG数据库的这段时间，总结了三种删除重复数据的方法，其中最容易想到的就是最常规的删除方法，但此方法性能较差，删数据耗时较久，虽容易实现，但性能太差，影响写数据的速率。另外就是被使用的groupby删除方法，效率较高。还有一种是刚发现的，还没有验证，现在就总结下这三种删除方法，并验证各自的执行效率。首先创建一张基础表，并插入一定量的重复数据。test=#createtabledeltest(idint,namevarchar(255));C... 继续阅读 >

2021
02-20

Postgresql 查看SQL语句执行效率的操作

Explain命令在解决数据库性能上是第一推荐使用命令，大部分的性能问题可以通过此命令来简单的解决，Explain可以用来查看SQL语句的执行效果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。Explain语法：explainselect…from…[where...]例如：explainselect*fromdual;这里有一个简单的例子，如下：EXPLAINSELECT*FROMtenk1;QUERYPLAN--------------------------------------------... 继续阅读 >

2021
02-20

PostgreSQL对GROUP BY子句使用常量的特殊限制详解

一、问题描述最近，一个统计程序从Oracle移植到PostgreSQL（版本9.4）时，接连报告错误：错误信息1：postgresqlgroupbyposition0isnotinselectlist.错误信息2：non-integerconstantinGROUPBY.产生错误的sql类似于：insertintosum_tab(IntField1,IntField2,StrField1,StrField2,cnt)selectIntField,0,StrField,'null',count(*)fromdetail_tabwhere...groupbyIntField,0,StrField,'null';其中，d... 继续阅读 >

2021
02-20

PostgreSQL中的VACUUM命令用法说明

每当PostgreSQL数据库中的表中的行被更新或删除时，死亡行会被遗留下来。VACUUM则会把它们除去来使空间能被重新利用。如果一个表没有被清空，它会变得臃肿，浪费磁盘空间而且会降低顺序表扫描的速度，而且在较小范围内也会降低索引扫描的速度。VACUUM命令只可以移除这些不再被需要的行版本（也被称为元组）。如果被删除事务的事务ID（存储在xmax系统列中）比仍然活跃在PostgreSQL数据库（或者共享表的整个集群）中最老的事务（xmin... 继续阅读 >

2021
02-20

PostgreSQL 如何查找需要收集的vacuum 表信息

前言通常，在PostgreSQL中，由于经常需要对表进行UPDATE和DELETE，因此表会产生碎片空间。在PostgreSQL中，使用VACUUM仅仅对需要执行VACUUM表将已删除的空间标识为未使用，以便以后重用这些空间，但是不能立即将占用的空间返还给操作系统，因此需要使用VACUUMFULL，才可以释放空间，并立即将空间返还给操作系统。实现脚本记录收集表创建CREATETABLEIFNOTEXISTStab_vacuum_record(sqltexttext);收集需要VACUUM表函... 继续阅读 >

2021
02-20

PostgreSQL备份工具 pgBackRest使用详解

前言pgBackRest是一款开源的备份还原工具，目标旨在为备份和还原提供可靠易用的备份。特性并行备份和还原备份操作期间压缩通常是其瓶颈所在。pgBackRest通过并行处理解决了备份期间压缩出现的瓶颈问题。本地远程操作自定义协议允许pgBackRest以最小化配置通过SSH在本地或者远程执行备份、还原和归档。并且该程序也通过协议层提供了PostgreSQL查询接口，以便于必须要再远程访问PostgreSQL，从而保证了其安全性能。全量，增量和差异... 继续阅读 >

2021
02-20

postgresql数据合并,多条数据合并成1条的操作

对于主表中一条记录，对应明细表中的96条数据，每一条数据相隔15分钟，明细中没96条数据对应主表中的一个日期trade_date,并且每条明细中有一个字段start_time,即明细中每96条数据中第一条数据中start_time为00:00，第二条为00:15,第三条为00:30，依次类推，直到23:45,现在要将明细表中的96条数据合并成24条，即第一条数据中start_time为00:00，第二条为01:00，第三条为02:00sql:selectmax(de.bid_num)report_num,concat(to_cha... 继续阅读 >

2021
02-20

postgreSQL使用pgAdmin备份服务器数据的方法

使用postgresql的时候，有时候需要备份数据，但是数据库又比较多，比如我们的数据库如下：这就很烦了，需要一种一键备份还原的方法来备份还原。1、备份备份可以使用pgadmin自带的备份服务器。右键服务器连接，点击备份服务器，再选择创建的文件与数据库编码（一般为UTF8）,即可，感觉比命令行的方式好用多了。当然也可以使用命令行的方式，以下来自百度，试过没有成功，觉得太麻烦了就放弃了，供参考：SQL备份pg_dumpall.exe-hlo... 继续阅读 >

2021
02-20

postgreSQL查询结果添加一个额外的自增序列操作

只在数据库postgre中实验了，亲测有效。ROW_NUMBER()OVER(ORDERBYcol_nameASC)ASids下面是我的实验截图：补充：postgresql给已有的字段创建添加自增，并且自增值从最大的id+1开始/*修改表的id属性为自增*/CREATESEQUENCEtest_id_seqSTARTWITH1INCREMENTBY1NOMINVALUENOMAXVALUECACHE1;startwith序列开始值incrementby递增值给表添加自增属性altertabletestaltercolumnidsetdefaultnextval('... 继续阅读 >

2021
02-20

PostgreSQL 实现sql放入文件批量执行

PostgreSQLsql放入文件，登入数据库之后批量执行1.建立测试sql：viaa.sql插入：猜测每条sql语句是用；分隔的，function中的多个；也会自动识别。createtabletb1(idinteger);insertintotb1selectgenerate_series(1,10);select*fromtb1;deletefromtb1whereid<3;select*fromtb1;2.将aa.sql放入./src/postgresql-9.3.5/src/tutorial下（./src/postgresql-9.3.5/src/tutorial是PostgreSQL自动识别的目录，当然也可... 继续阅读 >

2021
02-20

PostgreSQL三种自增列sequence,serial,identity的用法区别

这三个对象都可以实现自增，这里从如下几个维度来看看这几个对象有哪些不同，其中功能性上看，大部分特性都是一致的或者类似的。1、sequence在所有数据库中的性质都一样，它是跟具体的字段不是强绑定的，其特点是支持多个对个对象之间共享。sequence作为自增字段值的时候，对表的写入需要另外单独授权sequence（GRANTUSAGEONSEQUENCEtest_old_id_seq;）sequence类型的字段表，在使用CREATETABLEnew_tableLIKEold_table的时... 继续阅读 >

2021
02-20

PostgreSQL 自增语法的用法说明

mysql使用auto_increment的语法实现表字段自增。在PostgreSQL中，具有数据类型为smallserial，serial，bigserial的字段具有自增特性。createtablecompany(idserialprimarykey,nametextnotnull,ageintnotnull,addresschar(50),salaryreal);则在插入该表的时候，可以不插入id列，数据库系统会自动填充。insertintocompany(name,age,address,salary)values('huangbaokang',29,'ganzhou',100000);补充：[Postgres... 继续阅读 >

2021
02-20

解决postgresql 自增id作为key重复的问题

啥也不说啦，看代码吧~SELECTsetval(‘data_tracking_au_sec_id_seq',(SELECTMAX(id)FROMdata_tracking_au_sec)+1);data_tracking_au_sec_id_seq：可以再DDL查看到data_tracking_au_sec：表名补充：【Postgresql】-主键自增，插入数据时提示违背主键唯一性错误备忘录：1.主键自增，插入数据时提示违背主键唯一性报错原因是自增主键的序列值与当前主键的字段值不匹配，我们现在需要设置当前最大主键值为序列值，执行下面的语句... 继续阅读 >

2021
02-02

解决postgresql 序列跳值的问题

一、序列的创建CREATESEQUENCEseq_bm_menuidINCREMENT1MINVALUE1MAXVALUE999999999999999999START1CACHE5;大家从以上语句中可以看出当前序列的cache为5，那么这个cache是在什么时候起作用呢？二、遇到的序列跳值问题当我们的web应用访问postgresql数据库，使用nextval('seq_bm_menuid')获取序列值，然后插入到我们的业务表中时，发现业务表中该序列值对应字段的值不连续，以5为间隔发生跳跃，如图所示：三、做个小实验... 继续阅读 >

2021
02-02

Postgresql数据库之创建和修改序列的操作

创建序列（从1开始，递增幅度1，最大值无上限）：createsequencefl_user_seqincrementby1minvalue1nomaxvaluestartwith1;更改序列值（方法中两个参数分别是1.序列名字，2.序列修改后值）：selectsetval('fl_user_seq',88);NavicatPremium中序列位置如下图：补充：PostgreSQL序列增删改创建序列CREATESEQUENCEifnotexiststest_mergetable_id_seqINCREMENT1MINVALUE1MAXVALUE999999999START1CACHE1;//或... 继续阅读 >