Using NULL value in INSERT or UPDATE with prepared statements

Question

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

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.

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?):

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.

Solution

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

Comments

[analytik]

Maybe you're overworked or stressed. No wonder, if you work with PgSQL.

[Tomislav Nakic-Alfirevic]

Oh that was undeserved...it's no fault of PgSQL's that it can't read the developer's mind and insert null instead of 'null'. Furthermore, from my own experience, PgSQL caused fewer problems than any other RDBMS I've worked with: MSSQL, MySQL, Oracle, Derby, SQLite, to name a few...

[EAmez]

pg_exec has been replaced by pg_query and examples in official documentation references to pg_execute instead. @quassnoi maybe you could update your answer.

[Quassnoi]

@EAmez: I don't even have php installed on my machine these days to make sure it works. If you can verify the updated code on your machine, please feel free to edit the answer.