pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Trouble with null text fields!
6+ messages / 4 participants
[nested] [flat]

* Trouble with null text fields!
@ 1999-04-13 15:18 Glenn Waldron <gwaldron@wareonearth.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Glenn Waldron @ 1999-04-13 15:18 UTC (permalink / raw)
  To: pgsql-sql

Using Postgres 6.5 beta (snap 12 apr), on Linux i386.  I moved up from
6.4.2 when I couldn't get things working.

I'm having difficulty dealing with null text/varchar fields.  I need
to be able to interpret null values as the null string '' for the
purposes on concatenation.


1) ----
For example, the query:

    SELECT (field_one || field_two) from t1;

Will return the concatenation of the two fields.  If either of the fields
is null, it is interpreted as the empty string '' and the correct answer
is printed.  But:

    SELECT * from t1 where ( field_one || field_two = 'something' )

This does NOT work is either field_one or field_two is null.  Same result
with the textcat() function.


2) ----
Next I tried using "case", getting a parse error at or near "then":

    SELECT ( case field_one when null then '' else field_one end ) from t1;

This one gave me "ERROR: copyObject: don't know how to copy 704":

    SELECT ( case field_one when 'string' then 'other' else 'third' end)
from t1;



3) ---
I tried writing a function that takes a "text" type and returns '' is the
string
is null.  Never could successfully do a null test on a function parameter.


4) ----
I also tried writing my own concat function, and found that passing
null fields into a user function doesn't seem to work either.  So I tried
passing the whole thing in as a TUPLE, since you can determine whether a
field is null with the GetAttributeByName() call.

The creation:

    CREATE FUNCTION mycat(text,text) returns text as '/usr/.../file.so'
langauge 'sql';

This worked great, even with null values:

    SELECT mycat(field_one, field_two) from t1;

This crashed the backend, with a "Memory exhauted in AllocSetAlloc()" error:

    SELECT * from t1 where mycat(field_one, field_two) = 'something';

So I tried making an index, and got:  "DefineIndex(): Attibute t1 not found"

    CREATE INDEX t1_ix on t1 ( mycat(t1) text_ops );


Any help is appreciated!! Sorry for the novel!!  -glenn

---
Glenn Waldron, WareOnEarth Inc. * v 703/847-2969 * f 703/847-2538





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Trouble with null text fields
@ 1999-04-13 15:25 Glenn Waldron <gwaldron@wareonearth.com>
  1999-04-13 16:07 ` Re: [SQL] Trouble with null text fields José Soares <jose@sferacarta.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Glenn Waldron @ 1999-04-13 15:25 UTC (permalink / raw)
  To: pgsql-sql

Using Postgres 6.5 beta (snap 12 apr), on Linux i386.  I moved up from
6.4.2 when I couldn't get things working.

I'm having difficulty dealing with null text/varchar fields.  I need
to be able to interpret null values as the null string '' for the
purposes on concatenation.


1) ----
For example, the query:

    SELECT (field_one || field_two) from t1;

Will return the concatenation of the two fields.  If either of the fields
is null, it is interpreted as the empty string '' and the correct answer
is printed.  But:

    SELECT * from t1 where ( field_one || field_two = 'something' )

This does NOT work is either field_one or field_two is null.  Same result
with the textcat() function.


2) ----
Next I tried using "case", getting a parse error at or near "then":

    SELECT ( case field_one when null then '' else field_one end ) from t1;

This one gave me "ERROR: copyObject: don't know how to copy 704":

    SELECT ( case field_one when 'string' then 'other' else 'third' end)
from t1;



3) ---
I tried writing a function that takes a "text" type and returns '' is the
string
is null.  Never could successfully do a null test on a function parameter.


4) ----
I also tried writing my own concat function, and found that passing
null fields into a user function doesn't seem to work either.  So I tried
passing the whole thing in as a TUPLE, since you can determine whether a
field is null with the GetAttributeByName() call.

The creation:

    CREATE FUNCTION mycat(text,text) returns text as '/usr/.../file.so'
langauge 'sql';

This worked great, even with null values:

    SELECT mycat(field_one, field_two) from t1;

This crashed the backend, with a "Memory exhauted in AllocSetAlloc()" error:

    SELECT * from t1 where mycat(field_one, field_two) = 'something';

So I tried making an index, and got:  "DefineIndex(): Attibute t1 not found"

    CREATE INDEX t1_ix on t1 ( mycat(t1) text_ops );


Any help is appreciated!! Sorry for the novel!!  -glenn





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] Trouble with null text fields
  1999-04-13 15:25 Trouble with null text fields Glenn Waldron <gwaldron@wareonearth.com>
@ 1999-04-13 16:07 ` José Soares <jose@sferacarta.com>
  0 siblings, 0 replies; 6+ messages in thread

From: José Soares @ 1999-04-13 16:07 UTC (permalink / raw)
  To: Glenn Waldron <gwaldron@wareonearth.com>; +Cc: pgsql-sql



Glenn Waldron ha scritto:

> Using Postgres 6.5 beta (snap 12 apr), on Linux i386.  I moved up from
> 6.4.2 when I couldn't get things working.
>
> I'm having difficulty dealing with null text/varchar fields.  I need
> to be able to interpret null values as the null string '' for the
> purposes on concatenation.
>
> 1) ----
> For example, the query:
>
>     SELECT (field_one || field_two) from t1;
>
> Will return the concatenation of the two fields.  If either of the fields
> is null, it is interpreted as the empty string '' and the correct answer
> is printed.  But:
>
>     SELECT * from t1 where ( field_one || field_two = 'something' )
>
> This does NOT work is either field_one or field_two is null.  Same result
> with the textcat() function.
>
> 2) ----
> Next I tried using "case", getting a parse error at or near "then":
>
>     SELECT ( case field_one when null then '' else field_one end ) from t1;
>

This is the syntax that PostgreSQL understands:

select  case when field_one is null then  t||'?' else t||v end from t1;


>
> This one gave me "ERROR: copyObject: don't know how to copy 704":
>
>     SELECT ( case field_one when 'string' then 'other' else 'third' end)
> from t1;
>

Seems that PostgreSQL doesn't recognize this syntax yet.

>
> 3) ---
> I tried writing a function that takes a "text" type and returns '' is the
> string
> is null.  Never could successfully do a null test on a function parameter.
>
> 4) ----
> I also tried writing my own concat function, and found that passing
> null fields into a user function doesn't seem to work either.  So I tried
> passing the whole thing in as a TUPLE, since you can determine whether a
> field is null with the GetAttributeByName() call.
>
> The creation:
>
>     CREATE FUNCTION mycat(text,text) returns text as '/usr/.../file.so'
> langauge 'sql';
>
> This worked great, even with null values:
>
>     SELECT mycat(field_one, field_two) from t1;
>
> This crashed the backend, with a "Memory exhauted in AllocSetAlloc()" error:
>
>     SELECT * from t1 where mycat(field_one, field_two) = 'something';
>
> So I tried making an index, and got:  "DefineIndex(): Attibute t1 not found"
>
>     CREATE INDEX t1_ix on t1 ( mycat(t1) text_ops );
>
> Any help is appreciated!! Sorry for the novel!!  -glenn

Try this one:

create function coalesce(text) returns text as
'declare
 nonullo ALIAS FOR $1;
begin
        IF nonullo ISNULL THEN
            RETURN ''\\\\N'';
        ELSE
            RETURN nonullo;
        END IF;
 end;
' language 'plpgsql';





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* RE: [SQL] Trouble with null text fields
@ 1999-04-13 15:48 Michael Davis <michael.davis@tvguide.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Michael Davis @ 1999-04-13 15:48 UTC (permalink / raw)
  To: 'Glenn Waldron' <gwaldron@wareonearth.com>; pgsql-sql

Try this:

DROP FUNCTION nz(text);
CREATE FUNCTION nz (text) RETURNS text AS '
   BEGIN
      if ($1 is NULL) then
         return '''';
      else 
         return $1;
      end if;
   END; ' LANGUAGE 'plpgsql';

SELECT (nz(field_one) || nz(field_two)) from t1;

This has worked for me.  It is a little slow. 

	-----Original Message-----
	From:	Glenn Waldron [SMTP:gwaldron@wareonearth.com]
	Sent:	Tuesday, April 13, 1999 9:26 AM
	To:	pgsql-sql@postgreSQL.org
	Subject:	[SQL] Trouble with null text fields

	Using Postgres 6.5 beta (snap 12 apr), on Linux i386.  I moved up
from
	6.4.2 when I couldn't get things working.

	I'm having difficulty dealing with null text/varchar fields.  I need
	to be able to interpret null values as the null string '' for the
	purposes on concatenation.


	1) ----
	For example, the query:

	    SELECT (field_one || field_two) from t1;

	Will return the concatenation of the two fields.  If either of the
fields
	is null, it is interpreted as the empty string '' and the correct
answer
	is printed.  But:

	    SELECT * from t1 where ( field_one || field_two = 'something' )

	This does NOT work is either field_one or field_two is null.  Same
result
	with the textcat() function.


	2) ----
	Next I tried using "case", getting a parse error at or near "then":

	    SELECT ( case field_one when null then '' else field_one end )
from t1;

	This one gave me "ERROR: copyObject: don't know how to copy 704":

	    SELECT ( case field_one when 'string' then 'other' else 'third'
end)
	from t1;



	3) ---
	I tried writing a function that takes a "text" type and returns ''
is the
	string
	is null.  Never could successfully do a null test on a function
parameter.


	4) ----
	I also tried writing my own concat function, and found that passing
	null fields into a user function doesn't seem to work either.  So I
tried
	passing the whole thing in as a TUPLE, since you can determine
whether a
	field is null with the GetAttributeByName() call.

	The creation:

	    CREATE FUNCTION mycat(text,text) returns text as
'/usr/.../file.so'
	langauge 'sql';

	This worked great, even with null values:

	    SELECT mycat(field_one, field_two) from t1;

	This crashed the backend, with a "Memory exhauted in
AllocSetAlloc()" error:

	    SELECT * from t1 where mycat(field_one, field_two) =
'something';

	So I tried making an index, and got:  "DefineIndex(): Attibute t1
not found"

	    CREATE INDEX t1_ix on t1 ( mycat(t1) text_ops );


	Any help is appreciated!! Sorry for the novel!!  -glenn

	



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* RE: [SQL] Trouble with null text fields
@ 1999-04-13 16:01 Michael Davis <michael.davis@tvguide.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Michael Davis @ 1999-04-13 16:01 UTC (permalink / raw)
  To: pgsql-sql

Try this:
DROP FUNCTION nz(text);
CREATE FUNCTION nz (text) RETURNS text AS '
		BEGIN
		if ($1 is NULL) then
return """;
		else 
		return $1; end if;
	END; ' LANGUAGE 'plpgsql';
SELECT (nz(field_one) || nz(field_two)) from t1;
This has worked for me.  It is a little slow. 
	-----Original Message-----
	From:	Glenn Waldron [SMTP:gwaldron@wareonearth.com]
<mailto:[SMTP:gwaldron@wareonearth.com]> 
	Sent:	Tuesday, April 13, 1999 9:26 AM
	To:	pgsql-sql@postgreSQL.org <mailto:pgsql-sql@postgreSQL.org> 
	Subject:	[SQL] Trouble with null text fields

Using Postgres 6.5 beta (snap 12 apr), on Linux i386.  I moved up
from
6.4.2 when I couldn't get things working.
I'm having difficulty dealing with null text/varchar fields.  I need to be
able to interpret null values as the null string " for the purposes on
concatenation.

1)	----
	For example, the query:
	SELECT (field_one || field_two) from t1;
Will return the concatenation of the two fields.  If either of the
fields
is null, it is interpreted as the empty string " and the correct
answer
is printed.  But:
	SELECT * from t1 where ( field_one || field_two = 'something' )
This does NOT work is either field_one or field_two is null.  Same
result
with the textcat() function.

2)	----
	Next I tried using "case", getting a parse error at or near "then":
SELECT ( case field_one when null then " else field_one end ) from t1;
This one gave me "ERROR: copyObject: don't know how to copy 704":
SELECT ( case field_one when 'string' then 'other' else 'third' end)
from t1;


3)	---
I tried writing a function that takes a "text" type and returns "
is the
string
is null.  Never could successfully do a null test on a function
parameter.

4)	----
I also tried writing my own concat function, and found that passing
null fields into a user function doesn't seem to work either.  So I
tried
passing the whole thing in as a TUPLE, since you can determine
whether a
field is null with the GetAttributeByName() call.
The creation:
		CREATE FUNCTION mycat(text,text) returns text as
'/usr/.../file.so'
langauge 'sql';
This worked great, even with null values:
	SELECT mycat(field_one, field_two) from t1;
This crashed the backend, with a "Memory exhauted in AllocSetAlloc()" error:
SELECT * from t1 where mycat(field_one, field_two) = 'something';
So I tried making an index, and got:  "DefineIndex(): Attibute t1
not found"
	CREATE INDEX t1_ix on t1 ( mycat(t1) text_ops );

Any help is appreciated!! Sorry for the novel!!  -glenn
	



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] Trouble with null text fields!
@ 1999-04-18 17:51 Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 6+ messages in thread

From: Tom Lane @ 1999-04-18 17:51 UTC (permalink / raw)
  To: Glenn Waldron <gwaldron@wareonearth.com>; +Cc: pgsql-sql@hub.org

"Glenn Waldron" <gwaldron@wareonearth.com> writes:
> I'm having difficulty dealing with null text/varchar fields.  I need
> to be able to interpret null values as the null string '' for the
> purposes on concatenation.

> This one gave me "ERROR: copyObject: don't know how to copy 704":
>     SELECT ( case field_one when 'string' then 'other' else 'third' end)
> from t1;

That is a parsing bug that I have just fixed; should be OK in the next
6.5 snapshot.

There is a better way, namely the COALESCE function, which is really
a shorthand form of CASE:
	COALESCE(arg1,arg2,...,argN)
This gives you the first of the arg values that isn't NULL.  So
	COALESCE(field1, '')
will substitute '' for NULL values.

Don't ask me why the SQL standards committee chose to call it COALESCE.
Doesn't make sense to me either ;-)

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~1999-04-18 17:51 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-04-13 15:18 Trouble with null text fields! Glenn Waldron <gwaldron@wareonearth.com>
1999-04-13 15:25 Trouble with null text fields Glenn Waldron <gwaldron@wareonearth.com>
1999-04-13 16:07 ` Re: [SQL] Trouble with null text fields José Soares <jose@sferacarta.com>
1999-04-13 15:48 RE: [SQL] Trouble with null text fields Michael Davis <michael.davis@tvguide.com>
1999-04-13 16:01 RE: [SQL] Trouble with null text fields Michael Davis <michael.davis@tvguide.com>
1999-04-18 17:51 Re: [SQL] Trouble with null text fields! Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox