From: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
---|---|
To: | nolan(at)celery(dot)tssi(dot)com |
Cc: | pgsql general list <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Creating a functional index on a cast? |
Date: | 2003-04-30 03:48:30 |
Message-ID: | 20030430034830.GB24494@svana.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, Apr 29, 2003 at 08:31:51PM -0500, nolan(at)celery(dot)tssi(dot)com wrote:
> I have two large tables with related data, one of which has a key that is
> char(8), the other has the same key, but it is varchar(8).
>
> The tables come from two different legacy environments which are being
> merged, part of the work at this point is settling on a unified data
> dictionary. Side question: This is an 8 character membership ID which
> will always be eight characters long if defined but may occasionally be
> null, does it make any significant difference in storage or performance
> whether I use char(8) or varchar(8)?
Not significant, no.
> However, before I can rebuild all the data tables using either char or
> varchar uniformly, I needed to be able to update the table with the varchar
> field from values in the other table, but this seems to take forever, even
> though both tables are indexed on the key field.
>
> It doesn't appear that I can cast one of the keys to the other format and
> have it work efficiently unless I can also cast a functional index. But
> I get errors when I try to create an index like this:
Hmm, I usually can use a cast and it works ok (between char() and text).
Mind you, if the only difference in the spaces, you can just use trim on the
column without a join? Unless there is something else at work here...
If the join doesn't work, please post both the query and the explain.
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
> - Samuel P. Huntington
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-04-30 03:58:03 | Re: dump/restore to 7.4devel giving "[archiver (db)] error returned by PQputline" |
Previous Message | Martijn van Oosterhout | 2003-04-30 03:28:16 | Re: Simple question about messages |