Re: Automatic null values convertion in INSERT and WHERE statements - Mailing list pgsql-admin

From Bruno Wolff III
Subject Re: Automatic null values convertion in INSERT and WHERE statements
Date
Msg-id 20031119192735.GB22998@wolff.to
Whole thread Raw
In response to Automatic null values convertion in INSERT and WHERE statements  ("Tomasz Spyrczak" <cbsman@gnuos.net>)
List pgsql-admin
On Wed, Nov 19, 2003 at 11:00:25 +0100,
  Tomasz Spyrczak <cbsman@gnuos.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.

pgsql-admin by date:

Previous
From: "Tomasz Spyrczak"
Date:
Subject: Automatic null values convertion in INSERT and WHERE statements
Next
From: "Marc G. Fournier"
Date:
Subject: Re: Postgresql DB on a live cd