Professional Web Applications Themes

changing column from int4 to int8, what happens with indexes? - PostgreSQL / PGSQL

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 ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match...

  1. #1

    Default 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


    ---------------------------(end of broadcast)---------------------------
    TIP 9: the planner will ignore your desire to choose an index scan if your
    joining column's datatypes do not match

    David Teran Guest

  2. #2

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

    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
    YZE 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 doentation 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
    [url]http://www.fuhr.org/~mfuhr/[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 1: subscribe and unsubscribe commands go to [email]majordomopostgresql.org[/email]

    Michael Fuhr Guest

  3. #3

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

    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
    > YZE 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
    [url]http://www.fuhr.org/~mfuhr/[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 6: Have you searched our list archives?

    [url]http://archives.postgresql.org[/url]

    Michael Fuhr Guest

Similar Threads

  1. Changing all varchars column sizes in a database
    By Alexandre Courbot in forum MySQL
    Replies: 0
    Last Post: August 21st, 11:28 AM
  2. Multi-column indexes
    By Edmund Dengler in forum PostgreSQL / PGSQL
    Replies: 3
    Last Post: January 16th, 12:00 AM
  3. Changing the binding of a template column
    By Rob Edwards in forum ASP.NET Data Grid Control
    Replies: 2
    Last Post: October 17th, 02:06 PM
  4. changing the template column's position ?
    By Gary in forum ASP.NET Data Grid Control
    Replies: 4
    Last Post: October 15th, 09:46 PM
  5. Changing row backcolor based on a column value from dataset
    By Chris Bond in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: September 17th, 11:30 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139