Re: Query problem - explicit casts

"kurt miller" <miller_kurt_e(at)hotmail(dot)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