postgresql的.的简单介绍

postgresql----数组类型和函数

postgresql支持数组类型，可以是基本类型，也可以是用户自定义的类型。日常中使用数组类型的机会不多，但还是可以了解一下。不像C或JAVA高级语言的数组下标从0开始，postgresql数组下标从1开始，既可以指定长度，也可以不指定长度。且postgresql既支持一维数组，也支持多维数组，但是平时二维数组也就够用了。

创新互联公司是一家集网站建设,平罗企业网站建设,平罗品牌网站建设,网站定制,平罗网站建设报价,网络营销,网络优化,平罗网站推广为一体的创新建站企业，帮助传统企业提升企业形象加强企业竞争力。可充分满足这一群体相比中小企业更为丰富、高端、多元的互联网需求。同时我们时刻保持专业、时尚、前沿，时刻以成就客户成长自我，坚持不断学习、思考、沉淀、净化自己，让我们为更多的企业打造出实用型网站。

示例1.使用ARRAY构建数组类型

---1*4的一维数组test=#selectarray[1,2,3,4];

array  -----------{1,2,3,4}

(1 row)--2*2的二维数组test=#selectarray[[1,2],[3,4]];

array    ---------------{{1,2},{3,4}}

(1 row)--1*2的二维数组，基本类型为box矩形类型，且和上面相比box类型元素之间是以分号分隔的，其他所有类型的数据都是以逗号分隔的test=#selectarray[box'(1,1),(0,0)',box'(0,0),(-1,-1)'];

        array            -----------------------------{(1,1),(0,0);(0,0),(-1,-1)}

(1row)

示例2.创建一张表，字段包含数组类型

其中int[]表示数组长度无限制，int[4]表示数组长度为4.

test=#createtabletbl_array(aint[],bvarchar(32)[][],cint);CREATETABLEtest=#insertintotbl_array (a,b,c)values(array[1,2],array[[1,2,3],[4,5,6]],1);INSERT01test=#insertintotbl_array (a,b,c)values(array[1,2,3],array[[1,2],[4,5]],2);INSERT01test=#select*from tbl_array ;

a    |b| c ---------+-------------------+---{1,2}|{{1,2,3},{4,5,6}}|1 {1,2,3}|{{1,2},{4,5}}|2(2 rows)

test=#selecta[1],b[2]fromtbl_arraywherec=1;

a | b ---+---1|

(1 row)

test=#selecta[1],b[2][1]fromtbl_arraywherec=1;

a | b ---+---1|4(1 row)

test=#selecta[1],b[2][4]fromtbl_arraywherec=1;

a | b ---+---1|

(1row)

test=#updatetbl_arrayseta[1]=200wherea[1]=1;UPDATE1test=#selecta[1],b[2][4]from tbl_array ;

a  | b -----+---100|200|

(2rows)

也可以使用[下标:上标]方式来查询数组连续的某些元素。

test=#selecta[2:3]from tbl_array ;

a  -------{2}

{2,3}

(2 rows)

test=#selecta[1:3]from tbl_array ;

a    -----------{100,2}

{200,2,3}

(2rows)

数组操作符与函数

操作符

操作符描述示例结果

=相等 SELECT ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3]; t

不等于 select ARRAY[1,2,3] ARRAY[1,2,4]; t

小于 select ARRAY[1,2,3] ARRAY[1,2,4]; t

大于 select ARRAY[1,4,3] ARRAY[1,2,4]; t

=小于或等于 select ARRAY[1,2,3] = ARRAY[1,2,3]; t

=大于或等于 select ARRAY[1,4,3] = ARRAY[1,4,3]; t

@包含 select ARRAY[1,4,3] @ ARRAY[3,1]; t

@包含于 select ARRAY[2,7] @ ARRAY[1,7,4,2,6]; t

重叠（是否有相同元素） select ARRAY[1,4,3] ARRAY[2,1]; t

||数组与数组连接 select ARRAY[1,2,3] || ARRAY[4,5,6]; {1,2,3,4,5,6}

||数组与数组连接 select ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]]; {{1,2,3},{4,5,6},{7,8,9}}

||元素与数组连接 select 3 || ARRAY[4,5,6]; {3,4,5,6}

||数组与元素连接 select ARRAY[4,5,6] || 7; {4,5,6,7}

函数

函数返回类型描述示例结果

array_append(anyarray,anyelement)anyarray 在数组末尾追加元素 

SELECT array_append(ARRAY[1,2], 3);

{1,2,3}

array_cat(anyarray,anyarray)anyarray 连接两个数组 SELECT array_cat(ARRAY[1,2,3], ARRAY[4,5]); {1,2,3,4,5}

array_ndims(anyarray)int 返回数组维数 SELECT array_ndims(ARRAY[[1,2,3], [4,5,6]]); 2

array_dims(anyarray)text 返回数组维数的文本表示 SELECT array_dims(ARRAY[[1,2,3], [4,5,6]]); [1:2][1:3]

array_fill(anyelement,int[], [,int[]])anyarray使用提供的值和维度初始化一个数组，其中anyelement是值，第一个int[]是数组的长度，第二个int[]是数组下界，下界默认是1 SELECT array_fill(7, ARRAY[3], ARRAY[2]); [2:4]={7,7,7}

array_length(anyarray,int)int 返回数组指定维度的长度 SELECT array_length(array[1,2,3], 1); 3

array_lower(anyarray,int)int 返回数组指定维度的下界 SELECT array_lower('[0:2]={1,2,3}'::int[], 1); 0

array_position(anyarray,anyelement[,int])int 返回数组元素anyelement从数组的[,int]位置（默认为1）开始第一次出现在数组中的位置，数组必须是一维的 SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon'); 2

array_positions(anyarray,anyelement)int[] 返回元素在数组中的所有位置 SELECT array_positions(ARRAY['A','A','B','A'], 'A'); {1,2,4}

array_prepend(anyelement,anyarray)anyarray 在数组开头添加新的元素 SELECT array_prepend(1, ARRAY[2,3]); {1,2,3}

array_remove(anyarray,anyelement)anyarray 从数组中删除所有的指定元素，必须是一维数组 SELECT array_remove(ARRAY[1,2,3,2], 2); {1,3}

array_replace(anyarray,anyelement,anyelement)anyarray 替换指定数组元素为新的元素 SELECT array_replace(ARRAY[1,2,5,4], 5, 3); {1,2,3,4}

array_to_string(anyarray,text[,text])text 将数组元素使用分隔符连接为文本，NULL可以使用指定元素替换 SELECT array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*'); 1,2,3,*,5

array_upper(anyarray,int)int 数组指定维度的上届 SELECT array_upper(ARRAY[1,8,3,7], 1); 4

cardinality(anyarray)int 返回数组所有维度的长度总和，如果是空数组则返回0 SELECT cardinality(ARRAY[[1,2],[3,4]]); 4

string_to_array(text,text[,text])text[] 将文本使用分隔符分隔后转换为数组，如果指定第三个参数，则第三个参数在数组中被转换为NULL SELECT string_to_array('xx~^~yy~^~zz', '~^~', 'yy'); {xx,NULL,zz}

unnest(anyarray)setof anyelement 将数组元素转换为行 SELECT unnest(ARRAY[1,2]); 

1

2

unnest(anyarray,anyarray[, ...])setof anyelement, anyelement [, ...] 将多维数组转换为行集合，其中第一个数组显示为第一列，第二个数组显示为第二列，以此类推。但是这个函数只在from子句中使用 SELECT * from unnest(ARRAY[1,2],ARRAY['foo','bar','baz']); 

unnest | unnest

--------+----

1 | foo

2 | bar

| baz

PostgreSQL全文检索简介

PostgreSQL自带有一个简易的全文检索引擎，可以实现小规模数据量的全文检索功能。本文我们将引导介绍一下这个功能，对于小数据量的搜索这个功能是足够使用的，而无需搭建额外的ES等重量级的全文检索服务器。

PG的全文检索操作符是 @@ ，当一个 tsvector (文档)和 tsquery (条件)匹配时返回 true ，并且前后顺序无影响:

和普通的SQL查询一样，只要在 WHERE 条件中使用这个符号就代表使用全文检索条件筛选文档了。如:

@@ 操作符支持隐式转换，对于 text 类型可以无需强类型转换( ::tsvector 或 to_tsvector(config_name, text) )，所以这个操作符实际支持的参数类型是这样的:

tsquery 查询条件并不是简单的正则，而是一组搜索术语，使用并且使用布尔操作符 （AND）、 | （OR）和 ! （NOT）来组合它们，还有短语搜索操作符 - （FOLLOWED BY）。更详细的语法参见 此文档 。

此外，PostgreSQL还提供了两个相对简化的版本 plainto_tsquery 和 phraseto_tsquery 。

plainto_tsquery ( plainto_tsquery([ config regconfig, ] querytext text) returns tsquery )用户将未格式化的 text 经过分词之后，插入 符号转为 tsquery :

phraseto_tsquery ( phraseto_tsquery([ config regconfig, ] querytext text) returns tsquery )行为和 plainto_tsquery 行为类似，但是分词之后不是插入 而是 - (FOLLOWED BY):

使用索引可以加快全文检索的速度。对于全文检索来说，可选的索引类型是 GIN (通用倒排索引)和 GIST (通用搜索树)，官方文档更推荐使用 GIN索引 。创建一个 GIN 索引的范例:

也可以是一个连接列:

还可以单独创建一个 tsvector 列，为这个列创建索引:

除了普通的 ORDER BY 条件之外，PostgreSQL为全文检索提供了两个可选的排序函数 ts_rank([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ]) returns float4 和 ts_rank_cd([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ]) returns float4 ，以便实现基于 权重 的排序。

此外，对于PostgreSQL 9.6以上的版本还可以使用 RUM index 排序。(注意，这个是扩展，默认不包含)。

PostgreSQL默认的分词字典中并不包含中文分词字典，因此我们必须手工引入。目前一个比较好的项目是 zhparser ，同时这个插件也是阿里云的RDS默认包含的。安装和启用没什么好说的。值得一提的是分词配置参数。

在 CREATE EXTENSION 之后，必须配置分词参数才能正确进行分词和查找，否则什么都查不到。官方文档提供的一个配置策略是:

n,v,a,i,e,l 这几个字母分别表示一种token策略，只启用了这几种token mapping，其余则被屏蔽。具体支持的参数和含义可以用 \dFp+ zhparser 显示:

WITH simple 表示词典使用的是内置的simple词典，即仅做小写转换。根据需要可以灵活定义词典和token映射，以实现屏蔽词和同义词归并等功能。

比如我们看下面这个例子:

可以看到 江淮 这个词组在查询的时候被忽略了，我们启用 j (abbreviation,简称)再看看结果:

所以实际使用中要设置合理的token types，过少将导致搜索结果不准确，过多将导致性能下降。此外，还有一些诸如 短词复合: zhparser.multi_short = f 这一类的控制分词结果的选项，根据实际使用酌情开启。

postgresql如何备份数据库

postgresql中可以使用pg_dump来备份数据库。pg_dump是用于备份PostgreSQL数据库的工具。它可以在数据库正在使用的时候进行完整一致的备份，并不阻塞其它用户对数据库的访问。

用法：

pg_dump [选项]... [数据库名字]

一般选项:

-f, --file=FILENAME output file or directory name

-F, --format=c|d|t|p output file format (custom, directory, tar, plain text)

-v, --verbose 详细模式

-Z, --compress=0-9 被压缩格式的压缩级别

--lock-wait-timeout=TIMEOUT 在等待表锁超时后操作失败

--help 显示此帮助信息, 然后退出

--versoin 输出版本信息, 然后退出

示例：

备份数据库，指令如下：

pg_dump -h 164.82.233.54 -U postgres databasename C:databasename.bak

开始-运行-cmd 弹出dos控制台；然后 在控制台里，进入PostgreSQL安装目录bin下：

cd C:Program FilesPostgreSQL9.0bin

最后执行备份指令：

pg_dump -h 164.82.233.54 -U postgres databasename C:databasename.bak

指令解释：

pg_dump 是备份数据库指令，164.82.233.54是数据库的ip地址（必须保证数据库允许外部访问的权限哦~），当然本地的数据库ip写 localhost；

postgres 是数据库的用户名；databasename 是数据库名。

意思是导出到C:databasename.bak文件里，如果没有写路径，单单写databasename.bak文件名，那么备份文件会保存在C: Program FilesPostgreSQL9.0bin 文件夹里。

推荐学习《Python教程》。