如何在PostgreSQL中将empty转换为null？ [英] How to convert empty to null in PostgreSQL?
问题描述
我有一些类型为int的列，但值是空的。所以我想在插入数据库时将空转换为空。
我使用代码：
I have some columns type int, but value is empty. So I want to convert empty to null when I insert to database. I use code:
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...
为什么？
推荐答案
而欧文关于 NULLIF的答案非常好，它不能解决语法错误。
While Erwin's answer about NULLIF is awesome, it doesn't address your syntax error.
让我们看一下查询：
$query="Insert Into tr_view(name,age,month,year) values ({toDB($name)},{toDB($age)},{toDB($month)},{toDB($year)})
之前，您定义了一个名为 toDB 的函数。不幸的是，您在此处使用的语法是 not 而不是如何从双引号字符串中调用函数，因此curl和 toDB（位是
Earlier you defined a function called toDB. Unfortunately the syntax you are using here is not how to call a function from within a double-quoted string, so the curlies and toDB( bits are still being passed through. There are two alternatives:
-
使用
进行串联。：
$query='insert Into tr_view(name,age,month,year) values (' . toDB($name) . ',' . toDB($age) . ',' . toDB($month) . ',' . toDB($year) . ')')
您可以将可调用的变量插入到双引号字符串中：
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)})";
第一个是清晰而理智的，第二个是对陌生而彻头彻尾的疯狂含糊不清。
The first is clear and sane, the second is vague to the unfamiliar and downright insane.
但是，您仍然不应该像这样组装输入。您仍然可能容易受到 SQL注入攻击。您应该使用带参数化占位符的预准备语句。
However, you still should not be assembling input like this. You still may be vulnerable to SQL injection attacks. You should be using prepared statements with parameterized placeholders.
Postgres扩展使用 pg_prepare 。它们具有明显的优势，例如，允许您传递PHP null ，而不必担心所有的空检测和引用。
The Postgres extension uses pg_prepare for this. They have the distinct advantage of, say, allowing you to pass a PHP null instead of having to worry about all of that null-detection and quoting.
如果您坚持将 toDB 保持原样，请考虑添加其中一个 pg_escape _ 函数，例如 pg_escape_string ，用于构建带引号的字符串。
If you insist on keeping toDB as-is, consider adding one of the pg_escape_ functions, like pg_escape_string, to the thing that builds quoted strings.
这篇关于如何在PostgreSQL中将empty转换为null？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
