问题描述
有时我需要在表中插入一些空值，或者更新它们将值设置为 NULL.
Sometimes I need to insert into the table some null values, or update them setting the value to NULL.
我在 Postgres 文档的某处读到这不能完成，但可以使用默认值欺骗:
I've read somewhere in the Postgres documentation that this can't be done, but can be tricked with the default value:
pg_query("INSERT INTO my_table (col_a, col_b) VALUES ('whatever', default)
我知道在这个例子中我会得到相同的结果:
I know that in this example I'll have the same result with:
pg_query("INSERT INTO my_table (col_a) VALUES ('whatever')
但问题在于准备好的语句:
But the problem comes with prepared statements:
pg_prepare($pgconn, 'insert_null_val', "INSERT INTO my_table (col_a, col_b) VALUES ($1, 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 ($1, $2)");
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 applies to update statements.
我认为有一个解决方案，因为 pgmyadmin 可以做到(或者看起来可以).
I think there is a solution, because pgmyadmin can do that (or it seems like it can).
如果你想知道为什么我需要在我的表中使用空值，让我举一个例子(也许有比空值更好的方法?):
If you are wondering why I need to play with null values in my tables, let me throw an example (maybe there is a way better then the null value?):
假设我有一个带有 email 列的 users 表，该列可以为空，但具有唯一索引.2个空邮件相等，违反唯一约束，而2个NULL值不相等，可以共存.
Assume I have the users table with an email column, which can be empty, but has a unique index. 2 empty emails are equal and violate the unique constraint, while 2 NULL values are not equal and can coexist.
推荐答案
使用 php 的字面量 NULL 作为参数:
Use the php's literal NULL as a parameter:
pg_prepare($pgconn, 'insert_null_val', "INSERT INTO my_table (col_a, col_b) VALUES ($1, $2)");
pg_query($pgconn, 'insert_null_val', array('whatever', NULL));
这篇关于使用准备好的语句在 INSERT 或 UPDATE 中使用 NULL 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！