Skip site navigation (1) Skip section navigation (2)

Re: Cast null to int4 upgrading from Version 7.2

From: Jim Nasby <decibel(at)decibel(dot)org>
To: Neil Conway <neilc(at)samurai(dot)com>
Cc: demmons(at)instantbenefits(dot)com, pgsql-patches(at)postgresql(dot)org, 'Jason Rutherford' <jasonr(at)instantbenefits(dot)com>, Dave Horn <dhorn(at)instantbenefits(dot)com>
Subject: Re: Cast null to int4 upgrading from Version 7.2
Date: 2006-11-17 03:48:49
Message-ID: F366047D-42EA-46C8-9DA2-360698CDF3F8@decibel.org (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
On Nov 16, 2006, at 3:10 PM, Neil Conway wrote:
> Yes, this is a common problem for people upgrading from 7.2. I  
> think the
> long-term fix is to change your queries: comparing an integer with  
> '' is
> not sensible. That is:
>
> SELECT * FROM employee_table WHERE employee_id = 0;
>
> is the right way to write that query.
>
> As a temporary fix, I suppose you could hack pg_atoi() to treat an  
> empty
> string as zero (src/backend/utils/adt/numutils.c).

As a less invasive alternative, I *think* you could create an SQL  
function for casting text to int that treated '' as 0, and then  
replace the built-in CAST with that. You'd also need to make the cast  
implicit, which could cause other problems.

20k lines of code isn't all that much, though... you'll be much  
better off fixing it.
--
Jim Nasby                                            jim(at)nasby(dot)net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)



In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2006-11-17 05:01:06
Subject: Re: [PATCHES] replication docs: split single vs. multi-master
Previous:From: Jim NasbyDate: 2006-11-17 01:48:58
Subject: Re: [HACKERS] Replication documentation addition

pgsql-patches by date

Next:From: Bruce MomjianDate: 2006-11-17 05:01:06
Subject: Re: [PATCHES] replication docs: split single vs. multi-master
Previous:From: Tom LaneDate: 2006-11-17 01:23:35
Subject: Proposed patch for xact-vs-multixact bugs

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group