Topic: How do I insert a NULL?

[clay1]

I thought I had this fixed but the problem has come back.

I have a form which has about a dozen fields some are strings and some are integers

Not all of the fields will be filled out for each record.

My problem is inserting the record when some of the fields are empty

I put this in my script: 

if (trim($value) == '') {
    $_POST[$key] = 'null';
  }

And that works when the integers are empty, but when it's a string I end up with the string 'null' in my record. Then when doing something like:

if (!empty($leadsemailcc)){
or if(isset($leadsemailcc)

It's considering the string 'null' as NOT NULL

If I user $_POST['key'] = NULL; then the insert fails

How the heck does PG handle this stuff?

[btherl]

The important thing here is the interface you're using to PG and how you pass data to it.  And yep the string "null" is not null, it's the string "null".  PHP's null is also not the same as postgres null, though they share some similarities.

Anyway, how do you pass this data to postgres?

[clay1]

What do you mean by how do I pass the data?

I am using php and the following pg_query, but I'm not sure if that is what you are asking about

Code: [Select]

pg_query($conn, "INSERT into \"markets\" VALUES (DEFAULT, '{$market}', '{$leadsemail}', '{$leadsemailcc}', '{$invoiceemail}', '{$invoiceemailcc}', {$fullprice}, {$halfprice}, {$pricelevel1}, {$pricelevel2}, {$pricelevel3}, '{$owner}', {$fullpriceagefrom}, {$fullpriceageto}, {$halfpriceagefrom}, {$halfpriceageto}, {$pricelevel1agefrom}, {$pricelevel1ageto}, {$pricelevel2agefrom}, {$pricelevel2ageto}, {$pricelevel3agefrom}, {$pricelevel3ageto})");

[btherl]

Yep that's exactly it.  Here's two examples:

INSERT INTO table (textcol) values ('null')
INSERT INTO table (textcol) values (null)

The first query will insert the string 'null', the second will insert a null value.  The difference is the quotes.