Re: Automatic null values convertion in INSERT and WHERE statements

On Wed, Nov 19, 2003 at 11:00:25 +0100,
Tomasz Spyrczak <cbsman(at)gnuos(dot)net> wrote:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> 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.

Most likely it is inserting 0 which may just happen to be the default value.

> How can I make PostgreSQL 7.4 to behave like that?

You can use the keyword DEFAULT to insert the default value or NULL to
insert a null value.

> Similarily I want PostgreSQL to accept and automatically convert to "IS
> NULL" "where" statements like this:
>
> SELECT * FROM test WHERE field1='';

There is a GUC flag you can change to get = NULL replaced by IS NULL, but
there is nothing in postgres than will cause a comparison against an
empty string to be the same as IS NULL.

If you have controll over the SQL you should switch to using standard SQL.