Re: empty form fields, NULLS, quoting, postgreSQL inserts

On Fri, 28 Mar 2003 17:22:27 +0000, Peter Clarke wrote this well
considered message:
> Dan Jewett wrote:
>> Greetings all,
>>
>> I realize this revisits an old topic, but I haven't been able to put
>> together a definitive answer to my problem by checking other sources.
>>
>> Assuming that that the postgres table fields are configured to allow
>> NULLs, and I wish to allow certain form fields to remain unfilled....
>>
>> I'm having trouble getting NULLs into date/time fields and interval fields.
>>
>> I've gotten this far:
>>
>> $trk_length = $_POST['trk_length'];
>> if (empty($trk_length)) //or if($trk_length == '')
>> $trk_length = NULL;
>>
>> or
>>
>> $length_str = $_POST['trk_length'];
>> if (empty($length_str))
>> $trk_length = NULL;
>> else $trk_length = $length_str;
>>
>> The insert:
>>
>> $result = pg_query($conn, "INSERT INTO track (field1, field2, trk_length)
VALUES ('$var1', '$var2', '$trk_length')");
>>
>
> Only use quotes if you have a value:
>
> $length_str = $_POST['trk_length'];
> if (empty($length_str))
> $trk_length = NULL;
> else $trk_length = "'".$length_str."'";
>
> $result = pg_query($conn, "INSERT INTO track (field1, field2,
> trk_length) VALUES ('$var1', '$var2', $trk_length)");
>
>> This results in a "bad external representation ''." error for the
>> insert. If I use double quotes, ie. $trk_length = "NULL"; and
>> remove the single quotes from the $trk_length variable in the insert
>> statement, the insert succeeds. But now, if $trk_length is not
>> empty, the insert fails with a parse error on the : character in my
>> interval string.
>>
>> The same is true for trying to insert NULLS into a date or time
>> field in postgres. I believe I have a quoting problem, but I can't
>> figure it out. Setting the default values of the fields to NULL has
>> not seemed to help.
>>
>> Can someone lend some expertise on this? I've read of others people
>> struggling with this. If we had a good answer to this maybe we good
>> get it into the interactive docs?
>>
>> Thanks,
>> Dan
>>
>>
>> _________________
>> Visit Eva, Anne, and Dan at: http://www.thenormalfamily.net.
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Vince, Rod, and Peter thanks for your very helpful answers!

Dan
_________________
Visit Eva, Anne, and Dan at: http://www.thenormalfamily.net.