分类：PostgreSQL

2021
01-21

PostgreSQL的外部数据封装器fdw用法

数据封装器fdw（ForeignDataWrappers）在PostgreSQL中相当于oracle中的dblink，可以很方便的操作其他数据库中的数据。场景，在本地的test库中通过外部数据封装器fdw访问本地的testdb中的t2表本地库test用户u1，远程库test用户dbuser版本：postgres=#selectversion();version------------------------------------------------------------PostgreSQL11.1,compiledbyVisualC++build1914,64-bit(1行记录)... 继续阅读 >

2021
01-21

PostgreSQL 中的postgres_fdw扩展详解

通过postgres_fdw扩展，访问远程数据库表一、环境准备虚拟机(node107)：centos7、PostgreSQL10远程服务器(百度云服务BBC)：centos7、PostgreSQL10在本地虚拟机上访问远程服务器的数据表。二、配置连接（1）创建扩展:在本地107这个节点上创建扩展。[root@107~]#supostgresu:userpostgredoesnotexist[root@107~]#supostgresbash-4.2$psqlmydbpostgrescouldnotchangedirectoryto"/root":权限不够psql(10.7)Ty... 继续阅读 >

2021
01-21

PostgreSQL的B-tree索引用法详解

结构B-tree索引适合用于存储排序的数据。对于这种数据类型需要定义大于、大于等于、小于、小于等于操作符。通常情况下，B-tree的索引记录存储在数据页中。叶子页中的记录包含索引数据（keys）以及指向heaptuple记录（即表的行记录TIDs）的指针。内部页中的记录包含指向索引子页的指针和子页中最小值。B-tree有几点重要的特性：1、B-tree是平衡树，即每个叶子页到root页中间有相同个数的内部页。因此查询任何一个值的时间是相同的... 继续阅读 >

2021
01-21

Postgresql 如何选择正确的关闭模式

停止数据库的命令：pg_ctlstop-D$PGDATA[-mshutdown-mode]shutdown-mode有如下几种模式：1.smart:等所有的连接中止后，关闭数据库。如果客户端连接不终止，则无法关闭数据库。开启一个空会话：[root@localhost~]#su-postgres[postgres@localhost~]$psqlpsql(9.4.4)Type"help"forhelp.postgres=#用smart关闭数据库：[postgres@localhost~]$pg_ctlstop-D$PGDATA-msmartwaitingforservertoshutdown...... 继续阅读 >

2021
01-21

PostgreSQL copy 命令教程详解

报文介绍PostgreSQLcopy命令，通过示例展示把查询结果导出到csv文件，导入数据文件至postgresql。1.copy命令介绍copy命令用于在postgreSql表和标准文件系统直接传输数据。copy命令让PostgreSQL服务器直接读写文件，因此文件必须让PostgreSQL用户能够访问到。该命令使用的文件是数据库服务器直接读写的文件，不是客户端应用的文件，因此必须位于服务器本地或被直接访问的文件，而不是客户端位置。copyto命令拷贝表内容至文件... 继续阅读 >

2021
01-21

基于Postgresql 事务的提交与回滚解析

用过oracle或mysql的人都知道在sqlplus或mysql中，做一个dml语句，如果发现做错了，还可以rollback;掉，但在PostgreSQL的psql中，如果执行一个dml，没有先运行begin;的话，一执行完就马上提交了，不能回滚，这样容易导致误操作的发生，有没有什么办法避免这个风险呢？当然有，在psql中默认是打开自动提交的，我们可以关闭自动提交，方法如下：设置\setAUTOCOMMITofftest=#createtabletest1(xint);CREATETABLETime:0.593m... 继续阅读 >

2021
01-21

postgresql 如何关闭自动提交

postgresql中默认是自动提交的查看是否是自动提交：postgres=#\echo:AUTOCOMMITon关闭自动提交：postgres=#\setAUTOCOMMIToffpostgres=#\echo:AUTOCOMMIToff另一种方式就在会话开始的时候以begin开始相当于关闭了自动提交，以end或者commit结束就可以了补充：pg（hgdb）默认事务自动提交默认情况下，AUTOCOMMIT（自动提交）是开着的，也就是说任何一个SQL语句执行完毕后，它锁做的数据修改都会被立即提交，这种情况... 继续阅读 >

2021
01-21

Postgresql 解决pg掉电后无法重启的问题

机房断电后重启pg数据库，报如下错误：<2019-01-0122:42:59.240EST>LOG:databasesystemwasinterruptedwhileinrecoveryat2019-01-0122:00:58EST<2019-01-0122:42:59.240EST>HINT:Thisprobablymeansthatsomedataiscorruptedandyouwillhavetousethelastbackupforrecovery.<2019-01-0122:42:59.241EST>LOG:databasesystemwasnotproperlyshutdown;automaticrecoveryinprogress<2... 继续阅读 >

2021
01-21

PostgreSQL 恢复误删数据的操作

在Oracle中；删除表或者误删表记录；有个闪回特性，不需要停机操作，可以完美找回记录。当然也有一些其他的恢复工具；例如odu工具，gdul工具。都可以找回数据。而PostgreSQL目前没有闪回特性。如何在不停机情况下恢复误删数据。还好是有完整的热备份。本文描述的方法是：利用热备份在另一台服务器进行数据恢复；再导入正式环境；这样不影响数据库操作。这方法也适用在Oracle恢复。必须满足几个条件1、有完整的基础数据文件备份和归... 继续阅读 >

2021
01-21

PostgreSQL 日志文件的所在位置

1、查看postgresql.conf文件的配置，看日志文件在哪logging_collector=onlog_directory='pg_log'log_filename='postgresql-%a.log'log_truncate_on_rotation=o2、日志的分类pg_log（数据库运行日志）内容可读默认关闭的，需要设置参数启动pg_xlog（WAL日志，即重做日志）内容一般不具有可读性强制开启pg_clog（事务提交日志，记录的是事务的元数据）内容一般不具有可读性强制开启3、各个日志的作用（1）pg_log... 继续阅读 >

2021
01-21

PostgreSQL 打印日志信息所在的源文件和行数的实例

一直好奇在PG中,当输出错误日志时,如何能够附带错误信息所在的源代码文件名以及发生错误的代码行数.postgres.conf中,log信息冗余级别为"default(默认)",terse:表示更加简单的日志信息,verbose:表示更加冗余的日志信息(即:附带"文件名和行数)#log_error_verbosity=default#terse,default,orverbosemessages修改为下面的"verbose"即可.log_error_verbosity=verbose#terse,default,orverbosemessages修改后,... 继续阅读 >

2021
01-17

PostgreSQL 使用raise函数打印字符串

raise函数在PostgreSQL中，该函数用于打印字符串，类似于Java中的System.out.println()，Oracle中的dbms_output.put_line()。用法如下：raisenotice'Mynameis%,Iama%.','Lewis','coder';以上sql会在控制台输出MynameisLewis,Iamacoder.。如果是在DBeaver里使用该函数，则会在output的tab里输出字符串。raise后面的notice是级别，一共有debug/log/info/notice/warning/exception这些级别，可以任意指定一个级别。... 继续阅读 >

2021
01-17

PostgreSQL 实现给查询列表增加序号操作

利用ROW_NUMBER()over()给查询序列增加排序字段SELECTROW_NUMBER()over(ORDERbYbiztypenameDESC)ASnum,biztypenameFROM(SELECTDISTINCTbiztypenameFROMbizmaptype)t;效果如下：补充：PostgreSql使用自定义序列（Sequence）向表插入数据id自增长按照以往Oracle建表的流程，我们来新建表，并且向表中导入数据。1.创建Book表CREATETABLEbook(idINTEGERPRIMARYKEY,nameCHARACTERVARYING(50),priceDO... 继续阅读 >

2021
01-17

PostgreSQL完成按月累加的操作

背景统计某个指标，指标按照月进行累加，注意需要按省份和年份进行分组。方法一、使用自关联--with按月统计得到中间结果WITHymsAS(SELECTregionid,SUM(getnum)ASgetnum,SUM(dealnum)ASdealnum,to_char(qndate,'yyyy-MM')ASyearmonthFROMt_queuenumberGROUPBYregionid,to_char(qndate,'yyyy-MM')ORDERBYregionid,yearmonth)--查用子查询解决。SELECTs1.regionid,s1.yearmonth,getnum,dealnum,(SELECTSUM(getnu... 继续阅读 >

2021
01-17

基于postgresql查询某个字段属于哪个表的说明

一、场景还原前端小林问我，它从前端的返回的某些字段，想知道那个表有这个字段，它想操作这个表……二、sql语句SELECT*FROMinformation_schema.COLUMNSWHERECOLUMN_NAME='storename';SELECTc.relnameAS"表名",a.attnameAS"字段名",c.oidFROMpg_classcLEFTJOINpg_attributeaONa.attrelid=c.oidWHEREc.relnameNOTLIKE'pg_%'--ANDc.relname='kx_store'ANDa.attname='storename';补充：postgresql数... 继续阅读 >

2021
01-17

Postgresql 存储过程(plpgsql)两层for循环的操作

项目中遇到测试，需要造4500数据，而且需要分部门和日期，一个部门一天30条数据，剩下的铺垫数据可以一个部门一天100w左右数据，这里，每次变换部门，日期，需要操作至少300次，想到用存储过程写一个函数进行首先，了解存储过程的语法：CREATE[ORREPLACE]FUNCTIONname([[argmode][argname]argtype[{DEFAULT|=}default_expr][,...]])[RETURNSrettype|RETURNSTABLE(column_namecolumn_type[,...])... 继续阅读 >