Creating a functional index on a cast?

From: nolan(at)celery(dot)tssi(dot)com
To: pgsql-general(at)postgresql(dot)org (pgsql general list)
Subject: Creating a functional index on a cast?
Date: 2003-04-30 01:31:51
Message-ID: 20030430013151.3469.qmail@celery.tssi.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

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:

CREATE INDEX TEST_IND ON TEST (CAST (KEY AS VARCHAR));

This is mostly an academic question, to actually do the work I created
a work table from one of the tables converting the key to varchar(8)).
--
Mike Nolan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron Mayer 2003-04-30 01:33:06 Re: dump/restore to 7.4devel giving "[archiver (db)] error returned by PQputline"
Previous Message Mark Kirkwood 2003-04-30 01:26:05 Re: qsort (was Re: Solaris)