包含postgresql要求的词条

hibernate 连接 postgresql postgresql 的语句有特殊的要求 HQL 会自动转换成 postgresql 要求的语句么

会的，只要你写的是标准的HQL语句。

如果你在做数据库类型迁移，例如从sqlserver 换成postgreSQL，有些本地代码它是不会转换的，例如sqlserver中的dateadd函数。。

对于java数据库开发，建议你去了解一下JDiy，它是一个超轻量级的数据库框架。支持PostgreSql，操作数据库非常简单。

PostgreSQL 中 sequence 的用法

sequence 是 pg 自带的高效的自增id工具（也叫序列）。sequence 使用了轻量级锁的方式来做到高效自增id的，所以会比 UPDATE 行锁快。sequence 的返回数据类型默认是64位的整数，pg 10 可以自定 smallint, integer 或者是 bigint。

sequence 是可以保证自增数据不重复的，也就是说每次自增后都会持久化保存，那么为了继续提高性能，可以加上 CACHE 参数（默认为1），每个进程（连接）可以缓存一个子序列在当前进程内存里面，当子序列用完了才会去原序列取新的子序列。

这个用个例子简单说一下，创建 sequence temp_seq3 时用了 CACHE 10，A session 可以获取到的值是 1...10，B session 可以获取到的值是 11...20，那么获取顺序可能是 (A, A, B, A)，返回值是 (1, 2, 11, 3)，这个不是严格自增的序列，但可以保证回次返回都是唯一的，用了 CYCLE 参数的除外。

根据文档和源码( link 第80行)，缓存在内存里面的最大值(cached)是每个进程都不一样的，所以如果要求严格自增的服务不能用 CACHE。

这里写三种用 pg 做自增id的方式

测试命令，在 MacOS 上用 docker 开一个 pg 在里面运行一下 pgbench 脚本，其中 sql.sql 的内容替换成对应的压测脚本。

TPS: 986

TPS: 7332

TPS: 7451

看看微信的分布式id生成器，原理都差不多，"实际应用中每次提升的步长为10000" = "CACHE 10000"。 link

以上优化已经是用在友好速搭电商系统的订单号和优惠券号生成服务中，为商家提供更快更可靠的服务。

如何在Windows平台上的安装PostgreSQL

PostgreSQL的现有版本在不增加可供购买的商业应用包时，并不降低其使用价值，反而由于它的plpgsql包和C及ODBC接口的存在，使数据库系统在二次开发的过程中，可最大程度地保护已经投入的程序资产。 宿主平台在Linux和类UNIX平台上，PostgreSQL8.2.4 比较适合 RedHat 的 AdvanceServer4和 Kylin2.1，这两个平台对于这一版本的PostgreSQL有最为接近的升级兼容性，两者的表示层都是BSD血统，使用的C库都在一个版本层中，系统的主要部分都很成熟，移植的问题基本上不存在。 对于Windows平台，目前典型的是2K和2K3，以及XP。在2K平台上兼容性极好，运行稳定，并且在数据库内部的操作与其它平台上的版本完全一致，这给数据库应用在不同平台之间的移植打下了良好的基础。 Windows平台安装实践所选择的平台是Windows2K。安装过程和一般应用程序的安装差不多，根据自己的需要指定安装选项，没有过多的要求。安装完毕并启动后，做以下几件事： (１)写一个文本文件，并命名为“pgpass.conf”，内容为： localhost:5432:postgres:postgres:qazwsxedc 其中“localhost”表示使用了UNIX域的Socket方式，这是为安全而考虑的。 第二个字段是端口，第三个字段是数据库名，第四个字段是帐户名，第五个 字段是口令。可以有多行，每行代表一个数据库帐户。 (２)上述文件放到以下路径： C:\Documents and Settings\postgresql\Application Data\postgresql 如果是不同的帐户，例如我使用的帐户是“campus”，那么路径为： C:\Documents and Settings\campus\Application Data\postgresql (３)打开ODBC数据源 -- 控制面板 -- 管理工具 -- ODBC数据源，在选项卡“用户DSN”中选择“添加”并打开，在列表中选择“PostgreSQL Unicode”并按“完成”。如果需要，也可以在选项卡“系统DSN”中按同一步骤添加。 (4)在列表中选择“PosgreSQL30W”，打开“配置”窗口，依次填入必要的信 息。在我的例子中，Database是“center”，Server是“Localhost”， Port是5432，UserName和Password根据自己的情况填写。 (5)按“Test”按钮，对话窗出现，显示“Connect successful”，然后按“Save”保存并退出。 (６)在VB、VC或是ASP中，按通常的方法选择数据源，这就完成了PostgreSQL在Windows系统上的全部安装过程。 我的应用数据库安装方法采用以文本文件的形式加上批处理过程来进行。关于建库的一段语句如下： select 'Create table space ...' ; CREATE TABLESPACE campus_center -- 创建表空间 OWNER campus LOCATION 'E:/cps/campus_center' ; select 'Create database Center ... ...' ; create database center -- 创建数据库 with template = template0 owner = campus tablespace = campus_center encoding = 'euc_cn' ; select 'Create Procedural Language PL/pgSQL ...' ; CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' -- 注册使用的解释器 HANDLER plpgsql_call_handler VALIDATOR plpgsql_validator其中，在建库时一定要指定参数“encoding = 'euc_cn'”，否则在Windows平台上会出现乱码。 批处理文件的内容大致如下： @echo off mem initdb.bat mkdir E:\cps\campus_center "D:\Program Files\PostgreSQL\8.2\bin\psql.exe" -h localhost -p 5432 postgres "postgres" -f .\00_wincenter_make.sql .\initwin.log 2 .\initwin2.log ... ...命令“mkdir”建立一个操作系统目录，作为将要建立的应用所使用的表空间。注意第二条命令“psql.exe”使用了全路径，后面的语句都是在一行上，不可中断。命令使用文件“00_wincenter_make.sql”来创建数据库，产生的标准输出和标准错误分别向文件“initwin.log”和“initwin2.log”中写入，可用于建库过程中的诊断。后续语句可仿此办理，但输出换向符应由“”改为“”，以追加信息到诊断日志尾部。 通常在诊断日志文件“initwin2.log”中找不到字符串“ERROR”时，可以认为应用数据库已经成功安装并运行了。 注意事项：如果在VB下使用PostgreSQL8.2.4，那么VB的版本要低于VS2005，否则接口不兼容。这也是所谓“.net”战略的排他性。

postgresql 建立索引

一、索引的类型：

PostgreSQL提供了多种索引类型：B-Tree、Hash、GiST和GIN，由于它们使用了不同的算法，因此每种索引类型都有其适合的查询类型，缺省时，CREATE INDEX命令将创建B-Tree索引。

1. B-Tree:

CREATE TABLE test1 (

id integer,

content varchar

);

CREATE INDEX test1_id_index ON test1 (id);

B-Tree索引主要用于等于和范围查询，特别是当索引列包含操作符" 、=和"作为查询条件时，PostgreSQL的查询规划器都会考虑使用B-Tree索引。在使用BETWEEN、IN、IS NULL和IS NOT NULL的查询中，PostgreSQL也可以使用B-Tree索引。然而对于基于模式匹配操作符的查询，如LIKE、ILIKE、~和 ~*，仅当模式存在一个常量，且该常量位于模式字符串的开头时，如col LIKE 'foo%'或col ~ '^foo'，索引才会生效，否则将会执行全表扫描，如：col LIKE '%bar'。

2. Hash：

CREATE INDEX name ON table USING hash (column);

散列(Hash)索引只能处理简单的等于比较。当索引列使用等于操作符进行比较时，查询规划器会考虑使用散列索引。

这里需要额外说明的是，PostgreSQL散列索引的性能不比B-Tree索引强，但是散列索引的尺寸和构造时间则更差。另外，由于散列索引操作目前没有记录WAL日志，因此一旦发生了数据库崩溃，我们将不得不用REINDEX重建散列索引。

3. GiST：

GiST索引不是一种单独的索引类型，而是一种架构，可以在该架构上实现很多不同的索引策略。从而可以使GiST索引根据不同的索引策略，而使用特定的操作符类型。

4. GIN：

GIN索引是反转索引，它可以处理包含多个键的值(比如数组)。与GiST类似，GIN同样支持用户定义的索引策略，从而可以使GIN索引根据不同的索引策略，而使用特定的操作符类型。作为示例，PostgreSQL的标准发布中包含了用于一维数组的GIN操作符类型，如：、=、等。

二、复合索引：

PostgreSQL中的索引可以定义在数据表的多个字段上，如：

CREATE TABLE test2 (

major int,

minor int,

name varchar

}

CREATE INDEX test2_mm_idx ON test2 (major, minor);

1. B-Tree类型的复合索引：

在B-Tree类型的复合索引中，该索引字段的任意子集均可用于查询条件，不过，只有当复合索引中的第一个索引字段(最左边)被包含其中时，才可以获得最高效率。

2. GiST类型的复合索引：

在GiST类型的复合索引中，只有当第一个索引字段被包含在查询条件中时，才能决定该查询会扫描多少索引数据，而其他索引字段上的条件只是会限制索引返回的条目。假如第一个索引字段上的大多数数据都有相同的键值，那么此时应用GiST索引就会比较低效。

3. GIN类型的复合索引：

与B-Tree和GiST索引不同的是，GIN复合索引不会受到查询条件中使用了哪些索引字段子集的影响，无论是哪种组合，都会得到相同的效率。

使用复合索引应该谨慎。在大多数情况下，单一字段上的索引就已经足够了，并且还节约时间和空间。除非表的使用模式非常固定，否则超过三个字段的索引几乎没什么用处。

三、组合多个索引：

PostgreSQL可以在查询时组合多个索引(包括同一索引的多次使用)，来处理单个索引扫描不能实现的场合。与此同时，系统还可以在多个索引扫描之间组成AND和OR的条件。比如，一个类似WHERE x = 42 OR x = 47 OR x = 53 OR x = 99的查询，可以被分解成四个独立的基于x字段索引的扫描，每个扫描使用一个查询子句，之后再将这些扫描结果OR在一起并生成最终的结果。另外一个例子是，如果我们在x和y上分别存在独立的索引，那么一个类似WHERE x = 5 AND y = 6的查询，就会分别基于这两个字段的索引进行扫描，之后再将各自扫描的结果进行AND操作并生成最终的结果行。

为了组合多个索引，系统扫描每个需要的索引，然后在内存里组织一个BITMAP，它将给出索引扫描出的数据在数据表中的物理位置。然后，再根据查询的需要，把这些位图进行AND或者OR的操作并得出最终的BITMAP。最后，检索数据表并返回数据行。表的数据行是按照物理顺序进行访问的，因为这是位图的布局，这就意味着任何原来的索引的排序都将消失。如果查询中有ORDER BY子句，那么还将会有一个额外的排序步骤。因为这个原因，以及每个额外的索引扫描都会增加额外的时间，这样规划器有时候就会选择使用简单的索引扫描，即使有多个索引可用也会如此。

四、唯一索引：

CREATE UNIQUE INDEX name ON table (column [, ...]);

五、表达式索引：

表达式索引主要用于在查询条件中存在基于某个字段的函数或表达式的结果与其他值进行比较的情况，如：

SELECT * FROM test1 WHERE lower(col1) = 'value';

此时，如果我们仅仅是在col1字段上建立索引，那么该查询在执行时一定不会使用该索引，而是直接进行全表扫描。如果该表的数据量较大，那么执行该查询也将会需要很长时间。解决该问题的办法非常简单，在test1表上建立基于col1字段的表达式索引，如：

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';

和上面的例子一样，尽管我们可能会为first_name和last_name分别创建独立索引，或者是基于这两个字段的复合索引，在执行该查询语句时，这些索引均不会被使用，该查询能够使用的索引只有我们下面创建的表达式索引。

CREATE INDEX people_names ON people ((first_name || ' ' || last_name));

CREATE INDEX命令的语法通常要求在索引表达式周围书写圆括弧，就像我们在第二个例子里显示的那样。如果表达式只是一个函数调用，那么可以省略，就像我们在第一个例子里显示的那样。

从索引维护的角度来看，索引表达式要相对低效一些，因为在插入数据或者更新数据的时候，都必须为该行计算表达式的结果，并将该结果直接存储到索引里。然而在查询时，PostgreSQL就会把它们看做WHERE idxcol = 'constant'，因此搜索的速度等效于基于简单索引的查询。通常而言，我们只是应该在检索速度比插入和更新速度更重要的场景下使用表达式索引。

六、部分索引：

部分索引(partial index)是建立在一个表的子集上的索引，而该子集是由一个条件表达式定义的(叫做部分索引的谓词)。该索引只包含表中那些满足这个谓词的行。

由于不是在所有的情况下都需要更新索引，因此部分索引会提高数据插入和数据更新的效率。然而又因为部分索引比普通索引要小，因此可以更好的提高确实需要索引部分的查询效率。见以下三个示例：

1. 索引字段和谓词条件字段一致：

CREATE INDEX access_log_client_ip_ix ON access_log(client_ip)

WHERE NOT (client_ip inet '192.168.100.0' AND client_ip inet '192.168.100.255');

下面的查询将会用到该部分索引：

SELECT * FROM access_log WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';

下面的查询将不会用该部分索引：

一个不能使用这个索引的查询可以是

SELECT * FROM access_log WHERE client_ip = inet '192.168.100.23';

2. 索引字段和谓词条件字段不一致：

PostgreSQL支持带任意谓词的部分索引，唯一的约束是谓词的字段也要来自于同样的数据表。注意，如果你希望你的查询语句能够用到部分索引，那么就要求该查询语句的条件部分必须和部分索引的谓词完全匹配。 准确说，只有在PostgreSQL能够识别出该查询的WHERE条件在数学上涵盖了该索引的谓词时，这个部分索引才能被用于该查询。

CREATE INDEX orders_unbilled_index ON orders(order_nr) WHERE billed is not true;

下面的查询一定会用到该部分索引：

SELECT * FROM orders WHERE billed is not true AND order_nr 10000;

那么对于如下查询呢？

SELECT * FROM orders WHERE billed is not true AND amount 5000.00;

这个查询将不像上面那个查询这么高效，毕竟查询的条件语句中没有用到索引字段，然而查询条件"billed is not true"却和部分索引的谓词完全匹配，因此PostgreSQL将扫描整个索引。这样只有在索引数据相对较少的情况下，该查询才能更有效一些。

下面的查询将不会用到部分索引。

SELECT * FROM orders WHERE order_nr = 3501;

3. 数据表子集的唯一性约束：

CREATE TABLE tests (

subject text,

target text,

success boolean,

...

);

CREATE UNIQUE INDEX tests_success_constraint ON tests(subject, target) WHERE success;

该部分索引将只会对success字段值为true的数据进行唯一性约束。在实际的应用中，如果成功的数据较少，而不成功的数据较多时，该实现方法将会非常高效。

七、检查索引的使用：

见以下四条建议：

1. 总是先运行ANALYZE。

该命令将会收集表中数值分布状况的统计。在估算一个查询返回的行数时需要这个信息，而规划器则需要这个行数以便给每个可能的查询规划赋予真实的开销值。如果缺乏任何真实的统计信息，那么就会使用一些缺省数值，这样肯定是不准确的。因此，如果还没有运行ANALYZE就检查一个索引的使用状况，那将会是一次失败的检查。

2. 使用真实的数据做实验。

用测试数据填充数据表，那么该表的索引将只会基于测试数据来评估该如何使用索引，而不是对所有的数据都如此使用。比如从100000行中选1000行，规划器可能会考虑使用索引，那么如果从100行中选1行就很难说也会使用索引了。因为100行的数据很可能是存储在一个磁盘页面中，然而没有任何查询规划能比通过顺序访问一个磁盘页面更加高效了。与此同时，在模拟测试数据时也要注意，如果这些数据是非常相似的数据、完全随机的数据，或按照排序顺序插入的数据，都会令统计信息偏离实际数据应该具有的特征。

3. 如果索引没有得到使用，那么在测试中强制它的使用也许会有些价值。有一些运行时参数可以关闭各种各样的查询规划。

4. 强制使用索引用法将会导致两种可能：一是系统选择是正确的，使用索引实际上并不合适，二是查询计划的开销计算并不能反映现实情况。这样你就应该对使用和不使用索引的查询进行计时，这个时候EXPLAIN ANALYZE命令就很有用了。

PostgreSQL中的join

从join的基本语法如下：

由此可以知道join的类型有以下几种类型:

假设有以下两张表

Table A:

Table B:

要说连接，首先要知道一个概念——笛卡尔积。

在PostgreSQL中笛卡尔积就是 cross join ，这是上面join类型中没有提到的。

结果如下：

简单来说，笛卡尔积就是把两张表所有的行拼起来。3行的表与3行的表连接，连接表就是9行。

内连接的 inner 是可以省略不写的。内连接会把满足连接条件的t_a和t_b的行连接起来，作为连接表的一行。

结果如下：

只有满足条件：id相等的列才会出现在连接表里。

首先会执行内连接，然后把左表的行都放进连接表，对于没有满足条件的右表的行，用空值代替

结果如下：

首先会执行内连接，然后把右表的行都放进连接表，对于没有满足条件的左表的行，用空值代替

结果如下：

首先会执行内连接，然后把左表和右表的行都放进连接表，对于没有满足条件的右表的行，用空值代替；对于没有满足条件的左表的行，用空值代替。

结果如下：

on 后面只要跟上返回布尔值的条件表达式即可，没有强制要求一定要使用表中的列，类似于where条件。

using函数需要传入左表和右表都存在的列名。以上面的例子来说，

using(id)

等价于

on t_a.id = t_b.id

可以传入多个列名，就相当于用and连接多个条件。需要注意的点是：

结果如下：

将natural join放在连接条件里而不是连接类型里面，主要是因为natural join是隐含的使用了using，并且参数是两张表中所有同名的列。

结果如下：

关于请问安装MySQL和PostgreSQL对硬件的最低要求是什么

PostgreSQL是一个数据库，120G代表postgreSQL保存的数据占的空间有120G，如果没用到可以直接删除，对系统没影响。