From: | Bruno Wolff III <bruno(at)wolff(dot)to> |
---|---|
To: | Tomasz Spyrczak <cbsman(at)gnuos(dot)net> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: Automatic null values convertion in INSERT and WHERE statements |
Date: | 2003-11-19 19:27:35 |
Message-ID: | 20031119192735.GB22998@wolff.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Marc G. Fournier | 2003-11-19 19:54:08 | Re: Postgresql DB on a live cd |
Previous Message | Gary Stainburn | 2003-11-19 17:17:27 | recovering from backup + transaction log |