Lists: | pgsql-general |
---|
From: | "Dwight Emmons" <demmons(at)instantbenefits(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Cast null string '' to integer 0 |
Date: | 2006-08-17 21:06:51 |
Message-ID: | 20060817210643.YCUQ28427.mta10.adelphia.net@IBN |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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?
Any help would be appreciated
Dwight
From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | demmons(at)instantbenefits(dot)com |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Cast null string '' to integer 0 |
Date: | 2006-08-19 16:53:11 |
Message-ID: | 200608191653.k7JGrBf07062@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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?
You would have to hack the backend code to change this.
--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
From: | "Guy Rouillier" <guyr(at)masergy(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Cast null string '' to integer 0 |
Date: | 2006-08-19 22:38:19 |
Message-ID: | D4D1632DC736E74AB95FE78CD609007923B03A@mtxexch01.add0.masergy.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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?
--
Guy Rouillier
From: | Berend Tober <btober(at)seaworthysys(dot)com> |
---|---|
To: | Guy Rouillier <guyr(at)masergy(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Cast null string '' to integer 0 |
Date: | 2006-08-20 01:46:56 |
Message-ID: | 44E7BF10.5030001@seaworthysys.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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.