PostgreSQL：使用准备好的语句插入和更新行时使用'NULL'值 [英] Postgresql: using 'NULL' value when insert and update rows with prepared statements

问题描述

有时候我需要在表中插入一些空值，或者更新它们以将其值设置为NULL。

sometimes i need to insert into the table some null values, or update them setting the value to NULL.

我已经在PostgreSQL文档中的某个地方读到了无法完成，但可以使用默认值 tricket ：

I've read somewhere in the postgresql documentation that this cant be done, but can be tricket with the default value:

pg_query("INSERT INTO my_table (col_a, col_b) VALUES ('whatever', default)

ps：我知道在这个例子中我将得到与

p.s: 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 problems comes witht the 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 comes with the update query.

我认为有一个解决方案，因为pgmyadmin可以做到这一点（或者看起来可以做到），并且是用php编写的（我认为它无论如何都不会使用准备好的语句）

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)

如果您想知道为什么我需要在表中使用null值，请举个例子（也许有比null值更好的方法）：假​​设我有user表和email col：该电子邮件可以是空的，但是是唯一索引。.2个空电子邮件是等于并且违反了唯一约束，而2个 NULL 值不相等并且可以共存。 / p>

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 equal and violates the unique constraint, while 2 NULL values are not equal and can coexist.

推荐答案

使用 php 的原义 NULL 作为参数：

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));

这篇关于PostgreSQL：使用准备好的语句插入和更新行时使用'NULL'值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！