Thread: changing column from int4 to int8, what happens with indexes?

Hi,

well, i think the answer is simple 'it works' but i am not sure. When i
change the column type from int4 to int8, are the indexes still usable
or do i have to drop the indexes and create them again?

regards, David

On Tue, Jan 04, 2005 at 09:47:30AM +0100, David Teran wrote:

> well, i think the answer is simple 'it works' but i am not sure. When i
> change the column type from int4 to int8, are the indexes still usable
> or do i have to drop the indexes and create them again?

What happened when you tried it?  You can use EXPLAIN or EXPLAIN
ANALYZE to see if the planner will use an index.

If you changed the type with 8.0's ALTER TABLE ALTER COLUMN TYPE
then see the documentation for ALTER TABLE:

ALTER COLUMN TYPE

    This form changes the type of a column of a table.  Indexes and
    simple table constraints involving the column will be automatically
    converted to use the new column type by reparsing the originally
    supplied expression.

If you did something else, then what was it?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Tue, Jan 04, 2005 at 09:08:51AM -0700, Michael Fuhr wrote:
> On Tue, Jan 04, 2005 at 09:47:30AM +0100, David Teran wrote:
>
> > well, i think the answer is simple 'it works' but i am not sure. When i
> > change the column type from int4 to int8, are the indexes still usable
> > or do i have to drop the indexes and create them again?
>
> What happened when you tried it?  You can use EXPLAIN or EXPLAIN
> ANALYZE to see if the planner will use an index.

I forgot to mention that with versions of PostgreSQL prior to 8.0
you might have to use a type cast to get the planner to use an
index.  That is, you might have to do something like:

SELECT * FROM foo WHERE x = 12345::INT8;

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/