Search Postgresql Archives

Re: Cast null string '' to integer 0

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Guy Rouillier wrote:

Dwight Emmons wrote:
I am upgrading from Postgres 7.2 to 8.1.  We have multiple systems
already in place that took advantage of the implicit cast of a null
'' string to an integer of '0'.  It is not financially feasible for
us to modify all the instances. Does anyone know of a fix?

Well, if you want all your clients to interpret a null value in that
column as zero, can't you just update the column to actually contain a
zero for those rows?

I've had success for handling concatenation of null text strings (cf. "http://www.varlena.com/varlena/GeneralBits/84.php";) with

CREATE OR REPLACE FUNCTION public.textcat_null(text, text)
 RETURNS text AS
$BODY$
SELECT textcat(COALESCE($1, ''), COALESCE($2, ''));
$BODY$
 LANGUAGE 'sql' VOLATILE;
ALTER FUNCTION public.textcat_null(text, text) OWNER TO postgres;

CREATE OPERATOR public.||(
 PROCEDURE = "public.textcat_null",
 LEFTARG = text,
 RIGHTARG = text);

but for numerics I haven't been able to get a similar strategy to work as nicely. But my suggestion would be to experiment with something to

CREATE OR REPLACE FUNCTION public.numeric_add_null("numeric", "numeric")
 RETURNS "numeric" AS
$BODY$
 SELECT numeric_add(COALESCE($1, 0), COALESCE($2, 0));
$BODY$
 LANGUAGE 'sql' VOLATILE;

CREATE OPERATOR public.+(
 PROCEDURE = numeric_add_null,
 LEFTARG = NUMERIC,
 RIGHTARG = NUMERIC
);


It works if you can type cast:

test=# SELECT 1+NULL::NUMERIC;
?column?
----------
       1
(1 row)

but that may not gain you much for an existing application that you want to avoid doing a lot of re-writing.



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux