- 博客(52)
- 资源 (4)
- 收藏
- 关注
RSS订阅原创 PostgreSQL修改被视图引用的表的字段
在pg中，当我们需要修改表的某个字段时，如果该字段刚好被视图引用，必须先将引用的对象删除，才能修改对应的字段。例如：bill=# create table test_t (id int, info text, crt_time timestamp, c1 varchar(10)); CREATE TABLEbill=# create index idx_test_t on test_t(...
2020-01-21 11:20:49
7278
4
原创 PostgreSQL中的with递归（兼容oracle connect by）
oracle中的树状查询是比较常用的，通过connect by子句来实现。虽然在pg中默认不支持这种语法，但是可以用过with子句来实现类似的功能。with子句的RECURSIVE选项将WITH从单纯的句法便利变成了一种在标准SQL中不能完成的特 性。通过使用RECURSIVE，一个WITH查询可以引用它自己的输出 。例如下面这张表tbl：bill=# select * from tbl;...
2020-01-21 09:53:08
6265
原创 PostgreSQL create type(兼容oracle)
pg中支持创建自定义类型create type，虽然和oracle中的自定义类型语法上有所差异，但是pg中的功能可以完全覆盖oracle中的自定义类型。oracle create type：https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8001.htm#i2126568–创建自定义类型：SQL> c...
2020-01-21 09:49:36
5932
2
原创 PostgreSQL整数除法注意事项
整型除以整型，正常情况下当然得到的应该也是整型。一般数据库中也是这么干的。在pg中也是这样，例如1/2得到的结果便会是0，但是在实际应用中，我们往往需要得到的是0.5，而不是直接将小数部分省略掉。那么在pg中该怎么实现呢？我们可以使用float8, money, numeric来存储这种数值，或者在除法时，对操作数进行转换。例子：需要注意的是，在oracle中整数除法和pg中不同。–or...
2020-01-21 09:12:27
5045
原创 PostgreSQL全文检索位置匹配
pg中的全文检索功能十分丰富，例如我们想要搜索包含“速度与激情”的文档，但是我们都知道pg中的全文检索实质上是将语句进行切词，然后按照词组进行匹配查询的。所以这就会出现一个问题：我们直接查询包含“速度”&“激情”的文档会导致匹配到包含“速度”和“激情”两个单独词语的文档。那么碰到这种情况该怎么办呢？pg全文检索中支持位置匹配，例如“速度与激情”这个词，分词后是有位置信息的，我们期望搜到的...
2020-01-20 16:21:18
1922
原创 postgis——空间索引
空间索引是postgis中十分重要的功能，一个数据库中如果不支持索引那几乎是没法使用的。postgis中空间索引通过将数据组织到搜索树中来加快搜索速度，搜索树可以快速遍历以查找特定记录。对于空间的几何图形，不是通过btree索引来加速查询，而是通过gist索引。gist索引是通过r_tree的结构来实现对空间类型数据的索引查询，其结构类似于btree索引。r树介绍：https://zh.wik...
2020-01-20 15:05:02
2784
原创 postgis——空间关系
postgis不仅可以用来存储空间数据，更常用的是用来比较几何图形之间的关系。例如我们想要通过手机查看离自己最近的共享单车的为止，就是通过比较停车位置、街道之间的几何关系来实现。ST_EqualsST_Equals(geometry A, geometry B)用于测试两个图形的空间相等性。如果两个相同类型的几何图形具有相同的x、y坐标值，即如果第二个图形与第一个图形的坐标信息相等（相同），...
2020-01-20 15:02:44
2010
原创 postgis常用函数
获取几何图形元数据信息：ST_GeometryType(geometry) —— 返回几何图形的类型ST_NDims(geometry) —— 返回几何图形的维数ST_SRID(geometry) —— 返回几何图形的空间参考标识码postgis=# select name,ST_GeometryType(geom),ST_NDims(geom),ST_SRID(geom) from t_...
2020-01-20 15:01:22
1941
原创 postgis——SRID
SRID，即空间引用标识符。每个空间实例都有一个空间引用标识符 (SRID)。SRID 对应于基于特定椭圆体的空间引用系统，可用于平面球体映射或圆球映射。空间列可包含具有不同 SRID 的对象。然而，在使用 SQL Server 空间数据方法对数据执行操作时，仅可使用具有相同 SRID 的空间实例。从两个空间数据实例派生的任何空间方法的结果仅在这两个实例具有相同的 SRID（该 SRID 基于相...
2020-01-20 15:00:20
5272
原创 postgis——几何图形创建使用
–建表postgis=# create table t_gis(name varchar,geom geometry);CREATE TABLE–插入数据插入5种基本的gis类型：点（POINT）、线（LINESTRING）、多边形（POLYGON）、内含空洞的多边形（POLYGON with a hole）、图形集合（COLLECTION）postgis=# INSERT INTO...
2020-01-20 14:59:09
2352
原创 postgis安装
0、环境说明：主机名 IP 数据目录dokcer 192.168.17.xxx /home/itm_pg/pgsql10.3PostGIS是在对象关系型数据库PostgreSQL上增加了存储管理空间数据的能力的开源GIS数据库。GIS数据库，也叫空间数据库（Spatial database），是负责存储处理位置（空间）数据和非空间数据的数据库。1、安装proj[r...
2020-01-20 14:57:37
1690
原创 Greenplum执行计划
Greenplum执行计划和pg中的类似，但是由于gp是分布式的shared nothing架构，所以执行计划必然和pg还是有些区别。gp中查看SQL的执行计划也是通过explain语句，语法如下：Command: EXPLAINDescription: show the execution plan of a statementSyntax:EXPLAIN [ ANALYZE ...
2020-01-20 14:47:53
3321
2
原创 Greenplum实现nulls first/last改写
在pg中支持nulls first/last的写法，并且因为pg的索引中可以存储null值，所以这种写法还支持索引。而在Greenplum之前的版本中是不支持这种写法的：select * from tbl order by id nulls first; select id, last_value(key) over (partition by gid order by crt_ti...
2020-01-20 14:46:56
1728
原创 Greenplum索引使用详解
在pg中，我们会经常使用索引来加速查询，但是在Greenplum中对于索引的依赖却比较低，应该合理的使用。因为在gp中顺序扫描会很快，而索引扫描则是一种随即搜索的模式。Greenplum的数据分布在Segment上，因此每个Segment会扫描全体数据的一小部分来得到结果。通过表分区，要扫描的数据量可能会更少，因此索引使用的场景可能会更少。并且索引通常会增加一些数据库负担，它们使用存储空间并且在...
2020-01-20 14:45:40
4473
原创 Greenplum备份恢复
Greenplum作为一个分布式数据库，其备份恢复的方式和pg还是有一些区别。其中gp支持两种模式的备份恢复：并行和非并行。两者的区别在于：并行方式中每台Segment主机都同时将数据写入到自己本地的磁盘存储上。而非并行的方式数据必须通过网络从Segment被发送到Master，然后由master接待你把所有的数据写入它的存储中。因此弊端也很明显：除了把I/O限制在一台主机上之外，非并行备份还...
2020-01-20 14:44:23
1896
1
原创 Greenplum分区表
Greenplum中分区表其目的也是用来避免扫描大表的全部内容，而分区表能够提升查询性能。分区是不会更改数据在segments之间的物理分布，而是对大表进行逻辑上的划分。目前Greenplum只支持range分区和list分区，以及两种分区的组合分区。通过使用create table的partition by子句来创建分区表，然后就会创建一个父表和一系列子表。在内部，Greenplum会在父表和...
2020-01-20 14:42:51
4684
原创 Greenplum创建和管理表
Greenplum中创建表和pg中类似，不过区别在于其表中的行会被分布在不用的segment中。我们需要在创建表的时候指定分布策略。gp中的表有一些限制：1、UNIQUE和PRIMARY KEY约束必须和它们所在表的分布键和分区键（如果有）兼容。2、不支持外键，但是语法上允许外键约束，但不会起作用。3、用户在分区表上定义的约束将作为整体应用到分区表上。用户不能在该表的单独的部分上定义约束。...
2020-01-20 14:41:06
2318
原创 Greenplum表存储类型
Greenplum中支持行存储和列存储，支持堆表和AO（append only）表。堆表和AO表：1、堆表Greenplum中默认和pg中一样都是使用堆存储表，堆表最适合于较小的表，适合更新、插入较频繁的表，在OLTP类型负载下表现最好。另外堆表的所有变更都会产生REDO，可以实现时间点恢复。但是堆表不能实现逻辑增量备份（因为表的任意一个数据块都有可能变更，不方便通过堆存储来记录位点）。2...
2020-01-20 14:39:48
2781
原创 Greenplum创建表空间
Greenplum中创建表空间的方式和pg中有较大不同，因为gp中有表空间(tablespace)和文件空间(filespace)两个概念。我们可以这样去理解，因为在gp中master和segment节点中数据文件肯定得存放在不同的位置，所以我们得想办法将所有的文件系统位置的文件全部都表示起来，因此就引入了文件空间这样一个概念——Greenplum数据库中所有部件所需的文件系统位置集合被称作一个...
2020-01-20 14:38:23
2167
原创 Grennplum源码安装
0、说明以在同一台主机上的一个master节点和一个segment节点为例来说明安装方式。1、安装前准备工作所有节点执行：[root@test180 ~]# yum -y install rsync coreutils glib2 lrzsz sysstat e4fsprogs xfsprogs ntp readline-devel zlib zlib-devel openssl open...
2020-01-20 14:36:20
1579
原创 PostgreSQL相似文本查询
在实际应用中，我们常常会使用到相似搜索，例如图片、数组、文本等相似搜索。对于文本，可以使用pg_trgm插件来实现相似搜索。在使用pg_trgm插件来进行相似搜索前，我们需要知道相似度是什么，相似搜索中很关键的一个点是相似度。通过show_limit()可以查看相似度限制，通过set_limit可以设置相似度阈值。相似度值越大，表示需要的匹配度越高，1表示完全匹配。bill=# select ...
2020-01-20 09:03:55
3777
原创 PostgreSQL词频统计 ts_stat
在全文检索中，词频统计是一个比较常见的需求。例如我们需要分析两个文本的相似性时使用的TF(Term Frequency 词频)/IDF(Inverse Document Frequency 逆向文本频率)算法就需要统计词频。那么pg中如何在一堆文本中，找到热词，或者对词频进行分析呢？pg内置的ts_stat函数可以实现该功能，ts_stat函数用法如下：ts_stat(sqlquery te...
2020-01-19 17:24:52
2172
原创 PostgreSQL使用zhparser自定义分词
Zhparser是pg的一个中文全文检索插件，它基于简单中文分词（SCWS）实现中文解析器。我们在使用zhparser时常常会遇到的一个问题就是：我们想要分词的词语无法被识别。例如：bill=# SELECT * FROM ts_parse('zhparser', '支付宝使用很方便'); tokid | token -------+------- 118 | 支付 110 ...
2020-01-19 16:36:05
2670
原创 PostgreSQL约束延迟判断
当我们对一张表上的数据进行操作时，如果该表上有约束存在，那么约束是在什么时候生效的呢？例如我们在进行数据迁移的时候就会考虑的这个问题，有的迁移工具在迁移的时候必须得将表约束和数据分开迁移，否则会出现数据无法导入的情况，这就是因为约束不能延迟导致的。那么pg中对于约束的延判有什么原则呢？1、数据导出时，约束通常是在数据都写入后再创建。避免先创建约束后倒入失败。2、在使用过程中，PG提供了延迟...
2020-01-19 08:59:33
3620
原创 PostgreSQL exclude排它约束详解
pg中的exclude排它约束是用来保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将会返回否或空值。例如当我们表中某两列值要求是一对一的，即其中一列的值确定后，另一列的值也确定下来了，则可以使用exclude约束。语法为：CREATE TABLE circles ( c circle,EXCLUDE USING gist (c WITH &&am...
2020-01-17 17:30:18
8839
原创 PostgreSQL树状存储——ltree
ltree是pg内置的一个插件，实现了一种数据类型ltree用于表示存储在一个层次树状结构中的数据的标签。还提 供了在标签树中搜索的扩展功能。使用方法：1、安装extension，需要超级用户bill@bill=> create extension ltree; CREATE EXTENSION 2、支持的数据类型ltree (目前只支持A-Z,a-z,0-9,_作为lab...
2020-01-17 17:08:14
4429
原创 DM7建表指定填充因子(兼容oracle方法)
今天遇到开发人员询问一个问题，从oracle中把建表的语句复制到达梦中使用出现报错，语句如下：CREATE TABLE t1 ( ORDER_NUM VARCHAR2 ( 256 ) NULL, CUR_DATE VARCHAR2 ( 256 ) NULL, STAFF_NAME VARCHAR2 ( 256 ) NULL, ORG_NAME VARCHAR2 ( 256 ) NULL,...
2020-01-17 11:14:06
2470
原创 PostgreSQL归档配置及自动清理归档日志
在一般的生产环境中，数据库都需要开启归档模式，那么在pg中如何开启归档模式呢？pg中的归档配置涉及几个参数如下：# - Archiving - 是否开启归档 #archive_mode = off # enables archiving; off, on, or always # (cha...
2020-01-17 09:20:53
18470
原创 PostgreSQL dblink使用详解
相信对数据库比较熟悉的人都知道dblink，它支持了在一个数据库会话中连接到其他的数据库。虽然在pg中官方更加推荐使用postgres_fdw(它以一种更现代和更加兼容标准的架构提供了相同的功能)来替代dblink，但是pg中也同时提供了dblink这一功能，我们来看看pg中的dblink是如何使用的。1、安装pg中的dblink是自带的模块，直接安装该插件即可。bill@bill=>...
2020-01-16 15:13:25
13454
原创 PostgreSQL多维空间对象——cube插件
CUBE是pg内置的插件，实现了一种数据类型cube来表示多维立方体。CUBE是一个多维数据类型，支持两种多维类型：多维POINT、区间（左下+右上）。以及这些几何对象的几何特性搜索和计算（方位搜索、距离计算），这些搜索都支持GiST索引。例子：求多维点的距离：–欧几里得距离bill@bill=>select '(1,2,3,4)'::cube <-> '(2,2,3,1...
2020-01-16 10:12:01
2876
原创 自定义PostgreSQL登录提示符
通常我们在服务器上通过psql连接到pg的服务器时，会出现这样的提示：–使用bill用户登录bill数据库pg12@test180-> psql bill billpsql (12beta2)Type "help" for help.bill=# –使用postgres用户登录bill数据库pg12@test180-> psql bill postgrespsql ...
2020-01-15 17:07:47
1701
原创 PostgreSQL隐藏列(兼容oracle)
oracle 12cR1新增了隐藏列的功能，在之前的版本中，为了隐藏重要的数据字段以避免在通用查询中显示，我们往往会创建一个视图来隐藏所需信息或应用某些安全条件，现在我们可以通过隐藏列来实现这一功能。同时，当我们需要删除一张表的某一列时，如果系统正在允许时直接删除必然会锁表，且数据量大时会锁住很久。这时我们也可以通过隐藏列先将该列隐藏，因为该操作是直接修改数据字典，并没有正在删除数据，等闲时再d...
2020-01-15 09:52:39
2264
原创 mysql同步数据到PostgreSQL(使用mysql_fdw)
1、安装下载地址：https://github.com/EnterpriseDB/mysql_fdw修改环境变量：export MYSQLHOME=/usr/local/mysql export PGHOME=/home/pg12/pgsql12.4 export LD_LIBRARY_PATH=$PGHOME/lib:$MYSQLHOME/lib:/lib64:/usr/lib64...
2020-01-14 11:15:13
5597
原创 PostgreSQL索引膨胀
我们在使用数据库的时候经常会遇到索引膨胀的情况，一般我们都会选择去重建索引，这种情况在oracle、mysql等数据库中都经常发生，而在pg中也会有同样的问题，是什么原因导致索引膨胀呢？一般来说，通常是因为数据乱序写入导致的，我们都知道索引页中的数据是有序的，而索引字段乱序写入，会导致索引频繁分裂，使得索引页并不是百分百填满，自然出现索引膨胀的情况。在pg中的btree、gin、gist索引等...
2020-01-13 18:04:09
2980
原创 PostgreSQL不等于查询索引方法
我们都知道在数据库中不等于的查询是没有办法走索引的，所以一般使用<或>代替这样便可以走索引。那有没有办法让不等于查询走索引呢，方法是有的，只是这样性能并不见得比好。例子：建表及索引并插入测试数据：bill=# create table tbl1(id int primary key, c1 int); CREATE TABLEbill=# create index idx_...
2020-01-13 18:02:53
4163
1
原创 PostgreSQL查询相关性和相关性算法
当我们在搜索引擎查询时，经常会用到关键词搜索，同样，很多网站有标签的功能，会根据网页自动生成标签，标签实际上就是该网页的关键词。那么我们是如何通过关键词实现相关性搜索的呢？例如一个文档中出现频率越高的词是不是就是关键词呢？显然不是，一般一篇文档中出现最多的基本都是"的“、“是”、“你”等等这类词语，这些属于stop word，又比如一些词语“应用”、“文档”等等，这些词语虽然出现频率高，但是在大...
2020-01-13 18:01:39
1875
原创 PostgreSQL和mysql常用语法比较
1、分区表mysql和pg中的分区表使用基本类似，同样都支持hash、range、list三种基本的分区类型。两者的区别在于：mysql：不支持指定默认分区，最多只支持2级分区，不支持表达式分区。且需要注意，mysql当前除InnoDB或NDB之外的任何存储引擎都不支持分区表这一功能，如MyISAM。pg：pg中可以通过default分区名的方式指定默认分区，并且支持多级别的分区，且支持不同...
2020-01-13 17:32:25
7828
原创 PostgreSQL和mysql数据类型对比兼容
1、数值类型整数：mysql中的整数类型和pg相比，两者有以下区别：mysql：mysql中支持int 1,2,3,4,8 字节，同时支持有符号，无符号。并且mysql中支持在数值列中指定zerofill，用来将存储的数值通过填充0的方式达到指定数据类型的长度(mysql8开始不建议使用ZEROFILL属性，并且在将来的MySQL版本中将不再支持该属性)。pg：pg支持 int 2,4,8...
2020-01-13 15:18:37
10602
原创 PostgreSQL bit类型使用
位串即一串 1 和 0 的串。它们可以用于存储和可视化位掩码。我们有两种类型的 SQL 位类型:bit(n)和bit varying(n)，其中n是一个正整数。bit类型的数据必须准确匹配长度n; 试图存储短些或者长一些的位串都是错误的。bit varying(也可以写成varbit)数据是最长n的变长类型，更长的串会被拒绝。写一个没有长度的bit等效于 bit(1)，没有长度的bit var...
2020-01-13 15:14:45
7036
原创 PostgreSQL数据实时转换
pg中我们可以通过create rule的方式来实现将入库的数据实时转换成另一种格式，例如我们要存入json类型数据，可以直接存JSON，也可以存格式化数据，甚至可以在导入时，自动将JSON转成结构化数据。那么我们就可以使用实时数据转换的方式。但是rule的方式有一点不足，对于通过copy协议插入的数据无法做到实时转换，那我们该怎么办呢？通过pg中的触发器不失为一种好的方法。例子：1、rul...
2020-01-13 09:03:31
1737
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人