Re: Automatic null values convertion in INSERT and WHERE

On Wed, 19 Nov 2003, Tomasz Spyrczak wrote:

> I've just compiled PostgreSQL 7.4 and come across this problem:
>
> I have a table like this:
>
> CREATE TABLE test (field1 integer, field2 integer default 0);
>
> Then I want to insert some "empty" record like this:
>
> INSERT INTO test (field1, field2) VALUES ('', '');
>
> My question is: how to "convince" PostgreSQL to automatically convert (as it
> is mentioned in the documentation) the given empty values to the default
> values of field1 (to insert null value) and field2 (to insert default value
> of 0)? After default compilation PostgreSQL 7.4 gives an error while doing
> an insert query like this.
>
> Currently I am using pre-compiled PostgreSQL 7.2 from Linux Mandrake 9 and
> such an insert query described above works as desired - it inserts into a
> table default or null values without an error.

That's probably because in versions before 7.3 (IIRC the version it
changed on) an empty string was treated as 0 when converted to an integer.
This was considered inconsistent and removed. I don't believe that a GUC
option was added to change this behavior (or at least I don't see it).

> Similarily I want PostgreSQL to accept and automatically convert to "IS
> NULL" "where" statements like this:
>
> SELECT * FROM test WHERE field1='';
>
> I suppose this behavior can be change at the compile-time, but how please
> tell me :-(

Apart from making a view that has field1 as a textual type which converts
NULLs into empty strings, I think you'd have to do actual source editing.