我对学习PostgreSQL的18个差距的初步感受
首先
通过在PostgreSQL中进行聚合和分析数百万条数据的工作，我们总结出以下的经验。我们根据感觉到的差距和遇到的困难进行排序，所以文章的结构会自然而然地逐渐提高 🙂
这个内容是关于PostgreSQL和Oracle的比较。我尝试过两者，觉得PostgreSQL和Oracle都很棒！（ｷﾘｯ
2022年7月2日，我在公司同事岡部先生的共享中注意到布局出现了混乱，并收到了一些评论。我进行了一些修正。
数据操纵语言 (DML)
不需要句子就可以执行查询。
在Oracle中，需要使用 “from dual” 这个语句非常重要。
只需写上这么一句，就可以非常轻松地编写查询语句，让编程变得非常舒适。
> select 1 as a, 2 as b ,3 as c
+-----+-----+-----+
| a | b | c |
|-----+-----+-----|
| 1 | 2 | 3 |
+-----+-----+-----+
SQL> select 1 as a, 2 as b ,3 as c from dual;
A B C
---------- ---------- ----------
1 2 3
2. 获取序列的方法 de
在Oracle中，SQL语句必需包含from子句，所以需要指定dual表。
同时，可以通过<序列对象名>.nextval获取值。
> select nextval('seq_test') as seq_no;
+----------+
| seq_no |
|----------|
| 166 |
+----------+
这个差别并不太令人感到不舒服。
> select seq_test.nextval from dual;
NEXTVAL
----------
2
3. 隐式类型转换
由于PostgreSQL基本上不进行隐式类型转换，所以需要正确进行显式类型转换。
例如，类似于’1.00′ + 1的操作需要对字符串进行转换。
> select 1 + '1.0'
invalid input syntax for integer: "1.0"
LINE 1: select 1 + '1.0'
> select
cast('1.00' as float) + 1 as float
, cast('1.00' as numeric) + 1 as numeric
, cast('1.00' as double precision) + 1 as double
+---------+-----------+----------+
| float | numeric | double |
|---------+-----------+----------|
| 2 | 2.00 | 2 |
+---------+-----------+----------+
然而，如果没有小数点，PostgreSQL也会进行隐式类型转换。如果与整数类型匹配，则可以从前后左右进行运算，也没有问题。
> select 1 + '1' + 1
+------------+
| ?column? |
|------------|
| 3 |
+------------+
因为Oracle会努力地进行隐式类型转换，
所以无论小数点是否存在，精度是否不同，它都会进行运算。
> select 1 +'1.00' + 1 as result from dual;
RESULT
----------
3
4. 没有to_single_byte函数
在PostgreSql中，似乎只能通过使用translate函数进行替换。
这只是额外的一点工作，所以并不会带来太大的负担，但如果这是标准功能的话，可能会更加开心。
> select translate('１２３４５６７８９円です', '０１２３４５６７８９', '0123456789');
+-----------------+
| translate |
+-----------------+
| 123456789円です |
+-----------------+
顺便提一下，我还记得第一次从前辈那里学到 to_single_byte 函数的时候，由于它太方便了，我的灵魂都为之震动了。
> select to_single_byte('１２３４５６７８９円です') from dual;
TO_SINGLE_BYTE('１２３４５６７８９円
------------------------------------
123456789円です
5. 正则表达式在哪句子中的使用
如果您想在PostgreSQL中使用正则表达式对where子句进行过滤，它与Oracle的regexp_like不同，似乎使用波浪号作为替代方案。
这是常见的脚本语言中的正则表达式运算符，很容易理解，而且顺序也不错。
select * from table_nm where title ~ '[a-zA-Z]';
虽然Oracle的regexp_like也没有问题，但是有时会一时忘记它是一个函数而常常出错。（以为它是一个名为regexp_like的运算符。哈哈）
select * from table_nm where regexp_like(column_nm, '[a-zA-Z]');
在使用正则表达式进行字符串连接时，需要使用圆括号()进行括住。
如果想要用~运算符生成要在字符串连接中搜索的正则表达式，需要用圆括号()括起来。
select * where table_nm where column_nm1 ~ (column_nm2 || '[0-9]+.*');
用这种方式会导致语法错误，错误消息是“WHERE的参数必须是布尔类型，而不是文本类型”。
select * where table_nm where column_nm1 ~ column_nm2 || '[0-9]+.*';
ERROR: argument of WHERE must be type boolean, not type text
7. 获取当前日期和时间
在PostgreSQL中，有多种获取当前日期和时间的方法。
最好避免使用非标准的now()函数，而是使用current_xxx系列函数会更好。
由于现在()存在以下易陷入困境的情况，需要注意。
（注意）PostgreSQL的now()函数返回事务开始的时间
https://siguniang.wordpress.com/2013/05/17/how-do-i-return-current-timestamp-with-postgresql/
select CURRENT_TIMESTAMP as now
union all select now() as now -- 非標準
union all select TIMESTAMP 'now' as now;
+----------------------------------+
| now |
|----------------------------------|
| 2015-12-02 17:21:32.889558+09:00 |
| 2015-12-02 17:21:32.889558+09:00 |
| 2015-12-02 17:21:32.889558+09:00 |
+----------------------------------+
在Oracle中，即使也可以使用CURRENT_TIMESTAMP，但是我习惯于使用sysdate。
> select CURRENT_TIMESTAMP from dual
2 union all select sysdate from dual
3 ;
CURRENT_TIMESTAMP
----------------------------------------
15-12-02 17:30:14.330000 +09:00
15-12-02 17:30:14.000000 +09:00
8. 时间差异
在PostgreSQL中获取日期数据差异的方法是通过计算时间戳之间的差来获取日期差异，因为PostgreSQL中没有DATEDIFF函数。
查询中函数的嵌套使之难以阅读，但按以下顺序进行处理。
-
- 使用to_timestamp函数将时间字符串转换为时间戳并取差分。
-
- 使用to_char函数将时间戳差分转换为字符串。
- 由于上述步骤应该得到了分钟的差分，因此进行cast转换为数值。
以下以11:45和11:00之间的差异获取为例。
> select cast(to_char(to_timestamp('1145', 'hh24mi') - to_timestamp('1100', 'hh24mi'), 'mi') as double precision);
+-----------+
| to_char |
|-----------|
| 45 |
+-----------+
然而，上述的方法无法准确获取超过1小时的差值，例如11:45和10:00。我想获取的值是105分钟，但不慎获取了45分钟。
gis> select cast(to_char(to_timestamp('1145', 'hh24mi') - to_timestamp('1000', 'hh24mi'), 'mi') as double precision);
+-----------+
| to_char |
|-----------|
| 45 |
+-----------+
SELECT 1
Command Time: 0.000s
对于将这个转换为数字我不太清楚，但似乎使用提取epoc可以很好地解决这个问题。使用它可以正确获取到105分钟。
> select EXTRACT(EPOCH FROM to_timestamp('1145', 'hh24mi') - to_timestamp('1000', 'hh24mi')) / 60;
+------------+
| ?column? |
|------------|
| 105 |
+------------+
SELECT 1
由于Oracle的Date类型在整数部分存储日期，小数部分存储时间，因此只需提供小时×分钟即可。
我们可以使用trunc函数来舍弃小数部分。
> select trunc(to_number(to_date('1145', 'hh24mi') - to_date('1000', 'hh24mi')) * 24 * 60) as diff from dual;
DIFF
----------
105
9. 没有合并文。
尽管PostgreSQL默认不支持Merge语句，但可以通过在update语句中设置from/where子句来实现相同的功能。顺便提一下，根据规范，在set子句中不能使用表别名。当然，在where子句中是可以设置的，所以在功能上没有问题。
update
table_nm1 a
set
column_nm1 = column_nm2 -- ここではテーブルエイリアスは付与できない
from
table_nm2 b
where
a.id = b.id;
10. 聚合函数：按逗号分隔进行聚合
这是方言差异的问题。
string_agg似乎是从PostgreSQL 9.0开始引入的功能。
http://lets.postgresql.jp/documents/technical/9.0/2
select
string_agg(inv.col, ',')
from (
select '1' as col
union all select '2' as col
union all select '3' as col
) inv
+--------------+
| string_agg |
|--------------|
| 1,2,3 |
+--------------+
在Oracle中，我们使用listagg函数。即使不指定排序顺序，使用within group子句仍是必需的，这有点麻烦。
> select
2 listagg(inv.col, ',') within group(order by null)
3 from (
4 select '1' as col from dual
5 union all select '2' as col from dual
6 union all select '3' as col from dual
7 ) inv
8 ;
LISTAGG(INV.COL,',')WITHINGROUP(ORDERBYNULL)
----------------------------------------------------------
1,2,3
11. 如何使用last_value
如果在PostgreSQL中不给last_value选项适当的值，它将无法直观地工作。让我们在如下表格中试一试。
+-------+-------+
| age | grp |
|-------+-------|
| 15 | man |
| 20 | man |
| 22 | man |
| 17 | woman |
| 25 | woman |
+-------+-------+
当向这个问题提交下列查询时，无法获取到所需的last_val_1值。
似乎需要在last_value中指定RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。
我在这里了解到了原因。
http://postd.cc/window_functions_postgresql/#10
根据默认设置，PostgreSQL的窗口框架包括“从分区开头到当前行的最后一行”。这可能会导致last_value和nth_value产生不理想的结果。通过指定数据范围，可以避免这个问题，如以下示例所示。
select
age
, grp
, last_value(age) over(partition by grp order by age) as last_val_1 -- 誤った値が取得
, last_value(age) over(partition by grp order by age RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last_val_2
from (
select '15' as age, 'man' as grp
union all select '20' as age, 'man' as grp
union all select '22' as age, 'man' as grp
union all select '17' as age, 'woman' as grp
union all select '25' as age, 'woman' as grp
union all select '30' as age, 'woman' as grp
) inv
;
+-------+-------+--------------+--------------+
| age | grp | last_val_1 | last_val_2 |
|-------+-------+--------------+--------------|
| 15 | man | 15 | 22 |
| 20 | man | 20 | 22 |
| 22 | man | 22 | 22 |
| 17 | woman | 17 | 30 |
| 25 | woman | 25 | 30 |
| 30 | woman | 30 | 30 |
+-------+-------+--------------+--------------+
以下链接提供了一些有关于dew_rank和rownumber等聚合函数的行为的参考信息：
http://postd.cc/window_functions_postgresql/#10
12. 导出为CSV文件
可以使用复制命令将数据以CSV格式导出。
文件路径必须是可被PostgreSQL访问到的路径。
COPY (SELECT * FROM table_nm) TO '/tmp/output.csv' DELIMITER ','
13. 批量货运
copy函数可以批量加载数据。
在from语句中指定文件路径。
如果将header选项设置为true，它将识别CSV的第一行为列名并自动跳过。
COPY table_nm
FROM
'/var/temp/sample.csv'
WITH
(
FORMAT csv,
HEADER true, -- ヘッダあり
DELIMITER ',',
QUOTE '"',
ENCODING 'UTF-8'
);
数据定义语言 (DDL)
14. 列名的最大长度没有限制
因为在Oracle中有一个30个字符的限制，所以会引发错误。
由于这个限制，有时我们会使用省略元音的技巧，但在PostgreSQL中并没有系统上的限制。
即使列名超过60个字符也是可以的。
> create table table_nm (abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890 integer);
CREATE TABLE
Command Time: 0.000s
Format Time: 0.000s
当您搜索元数据时，即使超过60个字符也可以。
select
column_name
from
information_schema.columns
where
table_name ='table_nm'
;
+----------------------------------------------------------------+-----------+
| column_name | data_type |
+----------------------------------------------------------------+-----------+
| abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz1234567890 | integer |
+----------------------------------------------------------------+-----------+
在Oracle中肯定是不允许的。
> create table table_nm (abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890 integer);
Error
[row:1,col:20] ORA-00972: 識別子が長すぎます。
15. 数值型的定义 de
在使用numeric(4, 2)时，只能存储到10的2次方。
而在使用numeric(4)时，可以存储到10的4次方。
我注意到，精度的改变会影响能够存储的最大值，这一点我之前没有察觉到。
看来是我误解了。我得到了评论。
16. 更改列的位数
不需要修改句子。
alter table table_nm1 alter column column_nm1 type varchar(15);
数据字典
17. 获取表的元数据的方式
从数据库的字典中获取表名、列名、类型、长度、注释等信息的方法是如何操作PostgreSQL时最困扰我的地方之一。
可以生成用于搜索的表。
可以在PostgreSQL或Oracle上生成。
create table employee (
id char(5)
, name varchar(64)
, age numeric(3, 0)
, sex_type char(1)
, tel varchar(12)
, primary key(id)
)
;
COMMENT ON TABLE employee IS '社員マスタテーブル';
COMMENT ON COLUMN employee.id IS '社員ID';
COMMENT ON COLUMN employee.name IS '氏名';
COMMENT ON COLUMN employee.age IS '年齢';
COMMENT ON COLUMN employee.sex_type IS '性別区分';
COMMENT ON COLUMN employee.tel IS '電話番号';
似乎可以从pg_catalog模式或information_schema获取PostgreSQL的字典。
由于后者的information_schema更符合SQL标准，我想在那里完成，但是表注释和列注释只能从pg_catalog模式获取，因此需要创建混合查询。
SELECT
col.table_schema
, col.table_name
, tbl.description as table_comment
, col.ordinal_position
, col.column_name
, pgd.description as column_comment
, col.data_type
, col.column_default
, col.is_nullable
, col.character_maximum_length
, col.numeric_precision
, col.numeric_precision_radix
, col.numeric_scale
FROM
pg_catalog.pg_statio_all_tables sat
INNER JOIN
pg_catalog.pg_description pgd
ON (
pgd.objoid = sat.relid
)
INNER JOIN
pg_catalog.pg_description tbl
ON (
tbl.objoid = pgd.objoid
AND tbl.objsubid = 0
)
INNER JOIN
information_schema.columns col
ON (
col.ordinal_position= pgd.objsubid
AND col.table_schema = sat.schemaname
AND col.table_name = sat.relname
)
WHERE 1 = 1
AND col.table_name = 'employee'
ORDER BY
col.table_schema
,col.table_name
,col.ordinal_position
;
+----------------+--------------+--------------------+--------------------+---------------+------------------+-------------------+------------------+---------------+----------------------------+---------------------+---------------------------+-----------------+
| table_schema | table_name | table_comment | ordinal_position | column_name | column_comment | data_type | column_default | is_nullable | character_maximum_length | numeric_precision | numeric_precision_radix | numeric_scale |
|----------------+--------------+--------------------+--------------------+---------------+------------------+-------------------+------------------+---------------+----------------------------+---------------------+---------------------------+-----------------|
| public | employee | 社員マスタテーブル | 1 | id | 社員ID | character | <null> | NO | 5 | <null> | <null> | <null> |
| public | employee | 社員マスタテーブル | 2 | name | 氏名 | character varying | <null> | YES | 64 | <null> | <null> | <null> |
| public | employee | 社員マスタテーブル | 3 | age | 年齢 | numeric | <null> | YES | <null> | 3 | 10 | 0 |
| public | employee | 社員マスタテーブル | 4 | sex_type | 性別区分 | character | <null> | YES | 1 | <null> | <null> | <null> |
| public | employee | 社員マスタテーブル | 5 | tel | 電話番号 | character varying | <null> | YES | 12 | <null> | <null> | <null> |
+----------------+--------------+--------------------+--------------------+---------------+------------------+-------------------+------------------+---------------+----------------------------+---------------------+---------------------------+-----------------+
SELECT 5
如果在Oracle的情况下，如果在登录的模式中，则可以从以user_开头的表中获取数据。
如果想要访问其他模式的表，则可以从以dba_开头的表中获取数据。
这次我们特意访问了范围更广的dba_系列字典。
-- テーブルのメタ情報を検索
select
col.TABLE_NAME
, tct.COMMENTS as table_comment
, col.COLUMN_NAME
, cct.COMMENTS as column_comment
, col.DATA_TYPE
, col.DATA_LENGTH
, col.NULLABLE
, col.DATA_DEFAULT
from
DBA_TAB_COLUMNS col
, DBA_TAB_COMMENTS tct
, DBA_COL_COMMENTS cct
where 1 = 1
and col.OWNER = tct.OWNER
and col.TABLE_NAME = tct.TABLE_NAME
and tct.OWNER = cct.OWNER
and tct.TABLE_NAME = cct.TABLE_NAME
and col.COLUMN_NAME = cct.COLUMN_NAME
and lower(col.OWNER) = 'work_schema' -- ★スキーマ名を指定
and lower(col.TABLE_NAME) = 'employee' -- ★テーブル名を指定
order by
col.OWNER
, col.TABLE_NAME
, col.COLUMN_ID
;
-- 実行結果(Sqlplusでは上手く表示されなかったのでフォーマットが少し異なります)
+--------+------------+--------------------+-------------+----------------+-----------+-------------+----------------+----------+--------------+
| OWNER | TABLE_NAME | TABLE_COMMENT | COLUMN_NAME | COLUMN_COMMENT | DATA_TYPE | DATA_LENGTH | DATA_PRECISION | NULLABLE | DATA_DEFAULT |
+--------+------------+--------------------+-------------+----------------+-----------+-------------+----------------+----------+--------------+
| work_schema | EMPLOYEE | 社員マスタテーブル | ID | 社員ID | CHAR | 20 | | N | |
+--------+------------+--------------------+-------------+----------------+-----------+-------------+----------------+----------+--------------+
| work_schema | EMPLOYEE | 社員マスタテーブル | NAME | 氏名 | VARCHAR2 | 256 | | Y | |
+--------+------------+--------------------+-------------+----------------+-----------+-------------+----------------+----------+--------------+
| work_schema | EMPLOYEE | 社員マスタテーブル | AGE | 年齢 | NUMBER | 22 | 3 | Y | |
+--------+------------+--------------------+-------------+----------------+-----------+-------------+----------------+----------+--------------+
| work_schema | EMPLOYEE | 社員マスタテーブル | SEX_TYPE | 性別区分 | CHAR | 4 | | Y | |
+--------+------------+--------------------+-------------+----------------+-----------+-------------+----------------+----------+--------------+
| work_schema | EMPLOYEE | 社員マスタテーブル | TEL | 電話番号 | VARCHAR2 | 48 | | Y | |
+--------+------------+--------------------+-------------+----------------+-----------+-------------+----------------+----------+--------------+
JDBC 是 Java 数据库连接的缩写。
18. 大量的SELECT操作导致内存溢出
虽然我正在逐行处理ResultSet（而不是一次性将其加载到内存中），但仍然出现了OutOfMemory错误。
通过执行executeQuery方法时，似乎已经将所有的数据加载到了内存中。
我已经设置了setFetchSize，但它似乎没有起作用，所以一开始我感到困惑。
package example;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class App {
public static void main(String[] args) throws Exception {
Class.forName("org.postgresql.Driver");
Connection con = DriverManager.getConnection("jdbc:postgresql://<host>:<ip>/<sid>", "<user>", "<pass>");
PreparedStatement stmt = con.prepareStatement("select * from large_size_table"); // 大量件数のクエリ
stmt.setFetchSize(1000);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
String value1 = rs.getString("column_nm1");
String value2 = rs.getString("column_nm2");
String value3 = rs.getString("column_nm3");
System.out.println(value1 + "," + value2 + "," + value3);
}
}
}
当执行该操作时会出现内存不足的错误。
Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
at java.lang.Class.getDeclaredFields0(Native Method)
at java.lang.Class.privateGetDeclaredFields(Class.java:2583)
at java.lang.Class.getDeclaredField(Class.java:2068)
at java.util.concurrent.atomic.AtomicReferenceFieldUpdater$AtomicReferenceFieldUpdaterImpl$1.run(AtomicReferenceFieldUpdater.java:316)
at java.util.concurrent.atomic.AtomicReferenceFieldUpdater$AtomicReferenceFieldUpdaterImpl$1.run(AtomicReferenceFieldUpdater.java:314)
at java.security.AccessController.doPrivileged(Native Method)
at java.util.concurrent.atomic.AtomicReferenceFieldUpdater$AtomicReferenceFieldUpdaterImpl.<init>(AtomicReferenceFieldUpdater.java:313)
at java.util.concurrent.atomic.AtomicReferenceFieldUpdater.newUpdater(AtomicReferenceFieldUpdater.java:109)
at java.sql.SQLException.<clinit>(SQLException.java:372)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1817)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
at tig.exp.App.main(App.java:37)
通过将 Connection#setAutoCommit 设置为 false 来实现了避免。因为我只是想要进行数据库的查询，所以一开始并没有进行设置。但是如果不设置的话，Statement#setFetchSize 的设置会被禁用，而且似乎是会保留所有记录的规格。
Connection con = DriverManager.getConnection("jdbc:postgresql://<host>:<ip>/<sid>", "<user>", "<pass>");
con.setAutoCommit(false);
当设置Statement的fetchSize时，我们发现在设置con.setAutoCommit(false)以及stmt.setFetchSize(Integer.MAX_VALUE)等参数后，正如预期的那样，它能够有效地避免OutOfMemory错误。
最終结果是
使用Oracle约3、4年，但在功能方面，即使在使用PostgreSQL时也没有遇到什么大问题，可以顺利使用。
毕竟，它只是一种SQL框架，没有像过程式语言那样丰富多样，所以我想继续使用而不会太在意。
非功能差异方面，像PgCon的资料似乎很有趣。这是一份很棒的资料。
https://www.pgecons.org/
