Postgresql：在使用准备好的语句插入和更新行时使用“NULL”值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1027499/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql: using 'NULL' value when insert and update rows with prepared statements
提问by Strae
sometimes i need to insert into the table some null values, or update them setting the value to NULL.
有时我需要在表中插入一些空值，或者更新它们以将值设置为 NULL。
I've read somewhere in the postgresql documentation that this cant be done, but can be tricketwith the default value:
我在 postgresql 文档中的某处读到这不能完成，但可以使用默认值进行欺骗：
pg_query("INSERT INTO my_table (col_a, col_b) VALUES ('whatever', default)
p.s: i know that in this example i'll have the same result with
ps：我知道在这个例子中我会得到相同的结果
pg_query("INSERT INTO my_table (col_a) VALUES ('whatever')
But the problems comes witht the prepared statements:
但问题在于准备好的语句：
pg_prepare($pgconn, 'insert_null_val', "INSERT INTO my_table (col_a, col_b) VALUES (, default)");
pg_exec($pgconn, 'insert_null_val', array('whatever'));
//this works, but
pg_prepare($pgconn, 'insert_null_val', "INSERT INTO my_table (col_a, col_b) VALUES (, )");
pg_exec($pgconn, 'insert_null_val', array('whatever', 'NULL'));
//insert into the table the string 'NULL'.
//instead using array('whatever', '') it assume the col_b as empty value, not NULL.
The same problem comes with the update query.
更新查询也有同样的问题。
I think there is a solution, becose pgmyadmin can do that (or seem like it can), and is written i php (i dont think it doesnt use the prepared statements anyway)
我认为有一个解决方案，因为 pgmyadmin 可以做到这一点（或者看起来可以），并且是用 php 编写的（我认为它无论如何都不会使用准备好的语句）
If youre wondering why i need to paly with null values in my tables, let me throw an example (maybe there is a way better then the null value): assume i have the user table, and the email col: this one can be empty, but is a unique index.. 2 empty email are equaland violates the unique constraint, while 2 NULLvalues are not equal and can coexist.
如果你想知道为什么我需要在我的表中使用空值，让我举一个例子（也许有一种比空值更好的方法）：假设我有用户表和电子邮件列：这个可以是空的，但是是一个唯一索引。 2个空电子邮件是相等的并且违反了唯一约束，而2个NULL值不相等并且可以共存。
回答by Quassnoi
Use the php's literal NULLas a parameter:
使用php的文字NULL作为参数：
pg_prepare($pgconn, 'insert_null_val', "INSERT INTO my_table (col_a, col_b) VALUES (, )");
pg_exec($pgconn, 'insert_null_val', array('whatever', NULL));
