3

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Strae
  • 18,807
  • 29
  • 92
  • 131

1 Answers1

5

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));
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 1
    Maybe you're overworked or stressed. No wonder, if you work with PgSQL. – analytik Jul 20 '09 at 09:07
  • 6
    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... – Tomislav Nakic-Alfirevic Sep 25 '10 at 09:10
  • `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. – EAmez May 06 '21 at 08:01
  • @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. – Quassnoi May 06 '21 at 09:37