Query problem - explicit casts - Mailing list pgsql-general

Query problems.

Updating table A(fqhcdata) based on values in table B(chn_jmembrm0).

Keys:

Table A: sbrno - char(15)
Table B: subscriber_number - varchar

*both fields have indexes

==========================================================================
Query 1: (the most obvious solution)

update fqhcdata
set sbrno=( select distinct social_sec_no
            from chn_jmembrm0
            where subscriber_number=fqhcdata.sbrno::varchar );

Result:
000719.08:58:00.243 [20872] FATAL 1:  Memory exhausted in AllocSetAlloc()

==========================================================================
Query 2: (subselect test - explicit cast)

select distinct a.social_sec_no as ssn,
                b.fqhcdata.sbrno
from chn_jmembrm0 a,fqhcdata b
where a.subscriber_number=b.sbrno::varchar;

Result:
produces no matching rows

==========================================================================
Query 3: (subselect test - flipping cast)

select distinct a.social_sec_no as ssn,
                b.fqhcdata.sbrno
from chn_jmembrm0 a,fqhcdata b
where a.subscriber_number::char(15)=b.sbrno;

Result:
ERROR:  ExecInitIndexScan: both left and right ops are rel-vars

==========================================================================
Query 4: (workaround using temp tables)

select *,subscriber_number::char(15) as sbr
into tmp1
from chn_jmembrm0;

select distinct fqhcdata.sbrno,
       social_sec_no as ssn
into tmp2
from tmp1
where sbr=fqhcdata.sbrno;

update fqhcdata
set sbrno=(select ssn from tmp2 where fqhcdata.sbrno=sbrno);

Result:
Updates successfully

________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com