如何在PostgreSQL中将空值转换为null？

Question

我有一些int类型的列，但是它们的值是空的。所以当我插入到数据库时，我想将空值转换为null。 我使用的代码是：

function toDB($string) {
    if ($string == '' || $string == "''") {
        return 'null';
    } else {
        return "'$string'";
    }
}

//age,month,year is type integer.
$name="Veo ve";
$age='10';
$month='';
$year='';
    $query="Insert Into tr_view(name,age,month,year) values ({toDB($name)},{toDB($age)},{toDB($month)},{toDB($year)})
 $db->setQuery($query);
 $result= $db->query();

但它显示错误：

 pg_query(): Query failed: ERROR: syntax error at or near ";" LINE 153: {toDB(10)}, ^ in...

为什么？

- D T

---

您不能将''插入到int中，只能插入整数或null。 - Scott Marlowe

Answer 1

有一个NULLIF()函数：

SELECT NULLIF(var, '');

如果变量var等于第二个参数，则返回NULL。
该示例将空字符串''替换为NULL。

对于整数类型，不存在“空字符串”。两个参数必须是兼容的类型，因此在PHP中对输入进行清理。

如果您没有定义列默认值，还可以在INSERT命令中省略该列，它将被填充为NULL（这是默认的DEFAULT）。

在PHP中检查参数是否为空，如果为空，在INSERT命令中不要包含该列。

或者像Quassnoi在这里演示的那样，使用PHP字面NULL。

其余内容仅适用于字符串类型。为了确保绝对不能输入空字符串，请向表添加CHECK约束条件：

ALTER TABLE tr_view
ADD CONSTRAINT tr_view_age_not_empty CHECK (age <> '');

为了避免因此而引起的异常，您可以添加一个触发器来自动修复输入：

CREATE OR REPLACE FUNCTION trg_tr_view_avoid_empty()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF NEW.age = '' THEN
      NEW.age := NULL;
   END IF;

   IF NEW.month = '' THEN
      NEW.month := NULL;
   END IF;

   RETURN NEW;
END
$func$;

CREATE TRIGGER tr_view_avoid_empty
BEFORE INSERT OR UPDATE ON tr_view
FOR EACH ROW
WHEN (NEW.age = '' OR NEW.month = '')
EXECUTE PROCEDURE trg_tr_view_avoid_empty();

- Erwin Brandstetter

---

我知道感谢评论是不被鼓励的，但我还是要说一声谢谢。 - Benjamin Crouzier

Answer 2

虽然Erwin提到的NULLIF的答案非常好，但它没有解决您的语法错误。

让我们来看一下查询：

$query="Insert Into tr_view(name,age,month,year) values ({toDB($name)},{toDB($age)},{toDB($month)},{toDB($year)})

之前你定义了一个名为 toDB 的函数。不幸的是，你在这里使用的语法并不是在双引号字符串内调用函数的方法，因此花括号和 toDB( 部分仍然被传递。有两个替代方案：

1. Concatenation using .:

   $query='insert Into tr_view(name,age,month,year) values (' . toDB($name) . ',' . toDB($age) . ',' . toDB($month) . ',' . toDB($year) . ')')
   

2. You can interpolate a callable variable into a double-quoted string thusly:

   $fn = 'toDB';
   $query="Insert Into tr_view(name,age,month,year) values ({$fn($name)},{$fn($age)},{$fn($month)},{$fn($year)})";
   

第一个方法清晰明了，而第二个方法对不熟悉的人来说很模糊，甚至有些荒谬。

然而，您仍不应该像这样组装输入。您仍可能易受到 SQL注入攻击。您应该使用带参数占位符的准备语句。

Postgres扩展程序使用{{link3：pg_prepare}}实现此功能。它们具有独特的优势，例如允许您传递PHP null而无需担心所有的空值检测和引用。

如果您坚持保留toDB，请考虑将构建带引号字符串的内容之一添加到pg_escape_函数中，例如{{link4：pg_escape_string}}。

- Charles