Return-Path: postarch
Received: by postgres.Berkeley.EDU (5.61/1.29)
	id AA29905; Tue, 12 Nov 91 23:10:19 -0800
Message-Id: <9111130710.AA29905@postgres.Berkeley.EDU>
From: postarch (Postgres Mailing Archive)
Subject: Re: Problems with PQtrace() and NULL
To: postgres@postgres.berkeley.edu
Sender: pg_adm@postgres.berkeley.edu
In-Reply-To: Your message of "Tue, 12 Nov 91 14:56:21 EST."
             <9111130243.AA27103@postgres.Berkeley.EDU> 
Date: Tue, 12 Nov 91 23:10:04 PST

In message <9111130243.AA27103@postgres.Berkeley.EDU> you write:
> I've noticed a couple of problems with postgres 3.0.
> 
> First if yoy use the PQtrace() feature from libpq, you must make sure to
> set the external variable debug_port to the file pointer that you wish the
> output to be printed on. Otherwise your program will core dump in the fprintf
> statement in the debug routine. This doesn't seem to be documented anywhere.

The core dump is fixed for the next release. debug_port will default to 
stdout unless the application says otherwise.

> Secondly, I'm having trouble replaceing a tuple that was created with a NULL
> value for a char16 field. I've tried the following command:
> 
> replace address (street="leewood") where address.name="smith" and 
> address.street=NULL\g
> 
> this causes the backend to exit. Should i be casting something here? Is there
> something that would work with both null strings and zero length strings
> (i.e. "")?

Currently there's no straightforward way to either know about or take
advantage of null attributes in postgres queries.  You can only find
out about them after results are returned.

The language specification reserves keywords ISNULL and NOTNULL
functions, which return boolean, but these are not implemented.

There is, however, a work around you can use for attributes of type 
char16 and text.  There are undocumented regular expression 
operators ~ and !~ that you can use to select out null attributes 
in the following way:

	replace address (street="leewood") where address.name="smith" and 
	address.street !~ "..*"

the qualification address.street says the attribute street cannot match
the regular expression: ..*

Lastly, there is a bug in 3.0 with using these operators on attributes
of type text that will be fixed in 3.1.

Jeff Meredith
mer@postgres.berkeley.edu