Subject: Problems with PQtrace() and NULL

From: [email-masked] (Stephen Martin)
Date: Tue, 12 Nov 91 14:56:21 EST

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.

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. "")?
-- 
Stephen Martin               -------    LSI Logic Corporation of Canada, Inc.
Phone: (416) 620-7400     LSI|LOGIC|    Suite 1110, 401 The West Mall,        
Fax:   (416) 620-5005        |     |    Etobicoke, Ontario, Canada.
Email: [email-masked]   -------    M9C 5J5
 or    [email-masked]

---

From: postarch (Postgres Mailing Archive)
Date: Tue, 12 Nov 91 23:10:04 PST

> 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.

> something that would work with both null strings and zero length strings

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:

	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
[email-masked]