Thread: Query problem - explicit casts

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

"kurt miller" <miller_kurt_e@hotmail.com> writes:
> Updating table A(fqhcdata) based on values in table B(chn_jmembrm0).

> 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()

In 7.0 and before, I'd expect the cast here to leak memory, so if the
tables are large this result isn't surprising.  The leak is fixed for
7.1 but that won't help you today.  A more serious objection is that
it's not clear the subselect will produce exactly one row.

> 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

I think what's biting you here is that a varchar comparison will
consider trailing blanks to be significant --- so unless the
subscriber_number values are all pre-padded to 15 chars, they won't
match.  You could work around this by casting subscriber_number to
char(15) instead ... although I tend to think that this is just another
demonstration of the principle that using char(n) for variable-length
quantities is evil.  As an example, US postal abbreviations for states
(CA, PA, etc) are good char(2) material.  But anything that's even
potentially variable length should be varchar(n), NOT char(n).
Getting this right to begin with beats the heck out of finding
workarounds later.

> 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

I believe this is fixed in 7.0.2 --- what version are you running?

Anyway the bottom line is that changing the char(n) field to be
varchar(n) is probably the right answer.

            regards, tom lane