使用准备好的语句在 INSERT 或 UPDATE 中使用 NULL 值 [英] Using NULL value in INSERT or UPDATE with prepared statements

问题描述

有时我需要在表中插入一些空值，或者更新它们将值设置为 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 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！