本期的几个问答如下:
- PGJDBC版本与PG的对照关系
- 多值查询使用ANY替代IN
- PSQL查询结果美化输出
- UPSERT覆盖更新整行数据
- client_hostname字段没显示值
Q1. PGJDBC的版本与PG的版本如何对应?
问题描述
PG数据库服务器版本是9.6,客户端驱动PGJDBC如何选择版本,使用最新的版本是否能适配?
问题解答
PGJDBC是java程序,主要与java运行时的版本有关,根据jdk或jre的版本进行选择。
下面是PGJDBC官网对数据库版本支持的描述:
The current version of the driver should be compatible with PostgreSQL 8.4 and higher using the version 3.0 of the protocol and Java 8 (JDBC 4.2) or above.
PgJDBC regression tests are run against all PostgreSQL versions since 9.1, including “build PostgreSQL from git master” version.
PGJDBC通常都是向下兼容的,我们使用最新的版本即可。
Q2. PG里使用ANY替代IN有什么好处?
问题描述
IN是一个很常用的SQL操作符, 不过使用IN会有一些限制:对于标量表达式的变量列表,如果发送5个标量,必须写成IN ($1, $2, $3, $4, $5)。
除此以外,变量列表的个数也有限制,不能超过32768,否则会报错如下:
问题解答
如果我们把IN (…)等价替换为 ANY (…),数组可以作为单个参数绑定到ANY,就没有这个限制了。除了参数绑定之外,以下运算符也支持:
foo LIKE ANY ('{"%bar", “%baz”}')
foo ILIKE ANY ('{"%bar", “%baz”}')
id <> ANY ('{1, 2, 3}')
IN多值查询替换为ANY数组单个变量的方式除了突破变量列表个数的限制之外,还能提高性能。优化器也会在内部对IN改写为ANY。
Q3. PSQL里查询结果如何美化输出格式?
问题描述
看到有的PG文章里显示的PSQL查询结果比较美观:
搜索了一下,可能使用了下面的这个插件
https://postgres.cz/wiki/Pretty_borders_in_psql
不过这个插件现在下载不了。
问题解答
查看相关文档,使用下面两个设置。
\pset linestyle unicode
\pset border 2
对比下面默认的输出格式,上面的效果还是更美观一些。
Q4. UPSERT如何更新整行数据?
问题描述
当我们使用INSERT INTO … ON CONFLICT … DO UPDATE,对主键重复的记录行进行值覆盖时,需要使用excluded作为前缀,字段较多时比较繁琐,如果对整行进行覆盖,是否有更好的方式?
问题解答
整行可以使用excluded.*进行操作,测试语句如下:
CREATE TABLE t (id int primary key, info text);
INSERT INTO t VALUES (1, 'A');
INSERT INTO t values (1, 'B') ON CONFLICT (id) DO UPDATE SET (id, info) = ROW(excluded.*);
Q5. pg_stat_replication.client_hostname字段为什么没显示值?
问题描述
在主备环境下,打开了数据库参数log_hostname=on,备库操作系统也设置了hosts条目,但是主库查询pg_stat_replication系统视图client_hostname字段确没有显示值。
问题解答
数据库里显示hostname,除了打开log_hostname参数,备库的hostname也需要在主库的操作系统层进行设置。
示例如下:模拟两个备库,分别在主库节点100和节点200,主库只配置了节点100的hostname,节点200的被注释,那节点100能正常显示hostname,节点200则不能。