Re: Automatic null values convertion in INSERT and WHERE

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
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
Date: 2003-11-19 20:39:09
Message-ID: 20031119115602.F91425@megazone.bigpanda.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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.

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message John Gray 2003-11-19 23:05:38 Re: uploading files
Previous Message Mailer 2003-11-19 20:32:00 Re: Postgresql DB on a live cd