Thread: Automatic null values convertion in INSERT and WHERE statements

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi there!

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.

How can I make PostgreSQL 7.4 to behave like that?

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 :-(

Best regards
Tomasz Spyrczak

cbsman-at-gnuos.net

-----BEGIN PGP SIGNATURE-----
Version: PGP 8.0

iQA/AwUBP7s/LMbMsgKq/FYXEQKQwgCg3G6zzpBBAy+g2Rq5M9s6NeCQm68An3PG
GJe5jmiOhK+eWRD6hOAshj0o
=dOmw
-----END PGP SIGNATURE-----

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.

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.