Thread: 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
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 'declarenonullo ALIAS FOR $1; begin IF nonullo ISNULL THEN RETURN ''\\\\N''; ELSE RETURN nonullo; END IF;end; ' language 'plpgsql';
>>>> "Glenn" == Glenn Waldron <gwaldron@wareonearth.com> writes:
Glenn> Using Postgres 6.5 beta (snap 12 apr), on Linux i386. I moved up from Glenn> 6.4.2 when I couldn't get
thingsworking.
Glenn> I'm having difficulty dealing with null text/varchar fields. I need Glenn> to be able to interpret null
valuesas the null string '' for the Glenn> purposes on concatenation.
Glenn> 1) ---- Glenn> For example, the query:
Glenn> SELECT (field_one || field_two) from t1;
Glenn> Will return the concatenation of the two fields. If either of the fields
Glenn> is null, it is interpreted as the empty string '' and the correct answer Glenn> is printed.
Correct ?
I have a table:
david=> \d t
Table = t
+----------------------------------+----------------------------------+-------+| Field |
Type | Length|+----------------------------------+----------------------------------+-------+| a
| text | var || b | text
| var |+----------------------------------+----------------------------------+-------+
david=> insert into t values (NULL, '1111');
INSERT 26656 1
david=> insert into t values ('2222', NULL);
INSERT 26657 1
david=> select a||b from t;
?column?
--------
(2 rows)
But I expect something like:
1111
2222
not 2 empty strings. It is wrong ?
================================================
And yet, I found bug in cast. Try on table above:
select a::int from t;
... this will crash backend on field with NULL value ....
I have postgres 6.5beta1 compiled by egcs-1.1.2 on linux box with
IBM 6x86L processor ...
--
* David Sauer, student of Czech Technical University
* electronic mail: davids@orfinet.cz (mime compatible)