PostgreSQL 是世界上最强大的开源关系型数据库，学术气息浓厚，自由度极高。Nothing is perfect，这里记几个踩过的小坑。（规范使用并不会有这些问题，但是通用工具开发中需要考虑这些情况）

对象命名之坑

PostgreSQL 中非标准命名中允许使用各种符号（包括：% ? = + ' ''等等），但这种自由有点过火，尤其在开发管理工具过程中要兼容各种情况非常困难。

PostgreSQL对象标识符的命名规则
官方文档：https://www.postgresql.org/docs/12/sql-syntax-lexical.html
不管是按照标准命名还是非标准命名，同一个模式下的对象不能重名;
一、标准命名规则
标准命名规则要求：
1 SQL标识符和关键字必须以字母(a-z, but also letters with diacritical marks and non-Latin letters)和下划线(_)开头；
2 后续字符可以是字母(a-z)、下划线(_)、数字（0-9）或美元符号 ($);
3 不能是PostgreSQL关键字(注意：对于不同类型对象，有相同的关键字，也有不同的关键字不同);
注意：在 SQL 标准中：标识符中不允许使用美元符号，因此使用美元符号可能会使应用程序移植性差；关键字不能以数字或划线开头或结尾；因此，遵循SQL标准的标识符，可以安全地防止与标准的未来扩展发生冲突。
二、非标准命名规则
标准命名规则之外的命名都是非标准命名：
1 可以使用任何字符，包括保留关键字、中文、特殊符号（空格、*等）等；
2 必须用双引号引起来，否则会报语法错误；
注意：非标准命名必须用引号引起，不会产生语法错误。但此类命名不规范、移植性差、使用成本高，因此坚决不要日常使用。

1 在 SQL 语句的条件中：兼容带英文单引号的对象名称

//带WHERE条件的SQL语句
SELECT * FROM pg_class WHERE relname = 'identifer';

如果表示符号中含有单引号（如：tab‘1’），是无法使用的，解决办法有：

A 在语句SQL语句中，将标识符中的每个单引号前面加一个单引号作为转义，也作为防止SQL注入攻击的手段。

SELECT * FROM pg_class WHERE relname = 'tab''1''';

B 在Java中可以使用 PreparedStatement ，将标识符作为参数传入：

String sql = "SELECT * FROM pg_class WHERE relname = ?";
prepareStmt = conn.prepareStatement(sql);
prepareStmt.setString(1, "tab‘1’");
rs = stmt.executeQuery();

2 在JDBC URL 中：兼容带%特殊字符的数据库名称

参考：pgjdbc doc- Connecting to database
使用 JDBC时，数据库由 URL（统一资源定位器）表示。使用 PostgreSQL，这采取以下形式之一：
jdbc:postgresql:database
jdbc:postgresql:/
jdbc:postgresql://host/database
jdbc:postgresql://host/
jdbc:postgresql://host:port/database
jdbc:postgresql://host:port/
主机 : 服务器的主机名。默认为本地主机。要指定 IPv6 地址，必须将主机参数与方括号括在一起，例如：jdbc:postgresql://[::1]:5740/accounting
端口：服务器正在侦听的端口号。默认值为 PostgreSQL™标准端口号（5432）。
数据库：数据库名称。默认值是连接到与用户名同名的数据库。

注意：/? = & 是URL中用于设置和连接参数的符号，具有特殊含义。带有这些字符并非一定不能兼容，部分情况下也可兼容。

%默认不兼容，需要将每个%均替换为%25；

public static Connection getConnection(ConnInfo info) 
    throws ClassNotFoundException, SQLException
{
        final String driver = "org.postgresql.Driver";
        Class.forName(driver);

        Properties props = new Properties();
        props.setProperty("user", info.getUser());
        props.setProperty("password", pwd);

        String db = info.getBD().replaceAll("%", "%25");
        String = "jdbc:postgresql://" + info.getHost() + ":" + info.getPort() 
                 + "/" + db;
        Connection conn = DriverManager.getConnection(url, props);
        return conn;
    }

pgjdbc 中的数据类型之坑

PGJDBC作为JDBC的PostgreSQL实现，目前不支持分布式（需要用第三方的分布式中间件），很多细节也并不完善。此处，重点介绍在PreparedStatement的set类型上的坑：

a 空值的set方法：ppstmt.setNull(i, java.sql.Types.XX);

        logger.debug(datatype);
        switch (datatype)
        {
            //PreparedStatement sometime need setNull(index, type) to set null value.
            //reference pgjdbc - TypeInfoCache.java， PgPreparedSatement.java
            //binary types
            case "bytea":
                if (value == null) {
                    ppstmt.setNull(i, java.sql.Types.BINARY);
                } else {
                    ppstmt.setBinaryStream(i, (InputStream) value);
                } 
                break；
             //...
         }

b org.postgreslq.util.PGMoney中存在多年未解决的货币单位bug：

截止目前最新的pgjdbc中PGMoney源码中货币单位依然始终是$：

PGmoney(double value)
PGmoney(String value)
setValue(String s)
String getValue() 最明显的bug在此，任何时候返回的money单位都是$；

//设置参数，setMoney用法：有bug
ppstmt.setMoney(i,new PGMoney(value.toString()));
//获取结果
rs.getMoney(i);

正确用法：
//设置参数，在参数占位符？后面加上强制类型转换::money
String sql = "insert into salary2010(id, salary) values(?, ?::money)";
prepareStmt = conn.prepareStatement(sql);
ppstmt.setInt(1, 123);
ppstmt.setString(2, salary);//使用setString

PGmoney源码：

package org.postgresql.util;

import java.io.Serializable;
import java.sql.SQLException;
/**
 * This implements a class that handles the PostgreSQL money and cash types.
 */
public class PGmoney extends PGobject implements Serializable, Cloneable {
  /*
   * The value of the field
   */
  public double val;

  /**
   * @param value of field
   */
  public PGmoney(double value) {
    this();
    val = value;
  }

  public PGmoney(String value) throws SQLException {
    this();
    setValue(value);
  }

  /*
   * Required by the driver
   */
  public PGmoney() {
    setType("money");
  }

  public void setValue(String s) throws SQLException {
    try {
      String s1;
      boolean negative;

      negative = (s.charAt() == '(');

      // Remove any () (for negative) & currency symbol
      s1 = PGtokenizer.removePara(s).substring(1);

      // Strip out any , in currency
      int pos = s1.indexOf(',');
      while (pos != -1) {
        s1 = s1.substring(, pos) + s1.substring(pos + 1);
        pos = s1.indexOf(',');
      }

      val = Double.parseDouble(s1);
      val = negative ? -val : val;

    } catch (NumberFormatException e) {
      throw new PSQLException(GT.tr("Conversion of money failed."),
          PSQLState.NUMERIC_CONSTANT_OUT_OF_RANGE, e);
    }
  }

  @Override
  public int hashCode() {
    final int prime = 31;
    int result = super.hashCode();
    long temp;
    temp = Double.doubleToLongBits(val);
    result = prime * result + (int) (temp ^ (temp >>> 32));
    return result;
  }

  public boolean equals(Object obj) {
    if (obj instanceof PGmoney) {
      PGmoney p = (PGmoney) obj;
      return val == p.val;
    }
    return false;
  }

  public String getValue() {
    if (val < ) {
      return "-$" + (-val);
    } else {
      return "$" + val;
    }
  }

  @Override
  public Object clone() throws CloneNotSupportedException {
    // squid:S2157 "Cloneables" should implement "clone
    return super.clone();
  }
}

c 繁杂的数字类型和日期时间类型

PGJDBC对于PG中各种数字和日期时间类型的支持也并不明朗，所以在使用PreparedStatement 时候，建议通过在SQL语句的参数占位符号？加上强制类型转化，然后使用setString来设置参数，这样直接讲输入值传给pg去解析，以避免应用编码中不必要的类型转换和格式解析错误。

--数字类型--
--int2系列
smallint
smallserial，small autoincrementing integer
--int4系列：
integer
serial， autoincrementing integer
--int8系列
oid
bigint
bigserial，large autoincrementing integer
--
numeric
--
float4
real
--
float8
double precision


--日期时间类型--
--日期
date
--时间
time
time without time zone
timez
time with time zone
--时间戳
timestamp
timestamp without time zone
timestampz
timestamp with time zone

tid用作行数标识的风险

tid：是PG数据表中的一个伪列；表示表数据记录的物理行当信息，指的是一条记录的tuple的位置。跟oracle中伪列 rowid 的意义一样的，只是形式不一样。

vacuum：是PG的GC工具；回收dead tuples的物理位置，标识为可用。

tid用作行数标识的风险在于：当目标表执行vacumm后，表中存在零散的可用物理位置，导致行记录的tid的无规律变化。

例子：

postgres=# create table mytab(id int, name varchar);
CREATE TABLE
postgres=# insert into mytab values(1, 'yuan'),(2, 'shuo'), (3, 'an');
INSERT 0 3
postgres=# select ctid, id, name from mytab;
 ctid  | id | name 
-------+----+------
 (0,1) |  1 | yuan
 (0,2) |  2 | shuo
 (0,3) |  3 | an

##无vacumm
postgres=# delete from mytab where id=3;
DELETE 1
postgres=# insert into mytab values (4, 'lily');
INSERT 0 1
postgres=# select ctid, id, name from mytab;
ctid  | id | name 
-------+----+------
 (0,1) |  1 | yuan
 (0,3) |  3 | an
 (0,4) |  4 | lily
(3 rows)

##有vacumm
postgres=# delete from mytab where id=4;
DELETE 1
postgres=# vacuum myth;
postgres=# insert into mytab values (4, 'lily');
INSERT 0 1
postgres=# select ctid, id, name from mytab;
ctid  | id | name 
-------+----+------
 (0,1) |  1 | yuan
 (0,2) |  4 | lily
 (0,3) |  3 | an
(3 rows)

ITPub博客

PostgreSQL开发中的小坑

对象命名之坑

pgjdbc 中的数据类型之坑

tid用作行数标识的风险

最新文章