Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2009
    Posts
    12

    Unanswered: THE MOTHERLOAD of all problems

    Kudos to you if you can solve it because I've spent probably 10-15 hours on this.

    I have a table with a column that is type of char(9) in Postgres 8.4. I want to...I have to change this column type to varchar(16). I need to store more than 9 characters in this column now.
    The problem is that I have about 78 views that rely on this column and an endless amount of php files that use this column. I'm underestimating that number too. I don't want to go through and change them all one by one.

    How can I change this column type? The rules on the views are what's keeping me from doing an ALTER TABLE.

    I've tried everything I can, even altering the pg catalog. I've got nothing.
    I'm perfectly fine with spending time to create a script that will fix it. I just don't know how to fix it.

    Thanks in advance.

  2. #2
    Join Date
    May 2008
    Posts
    277
    I don't think there's any way around recreating all the views.

    One solution, depending on your circumstances, might be to dump your database, make the required change in the dump script, and then reload. This seems a bit ... extreme to change one column type, but maybe someone else has better advice.

    You could also use a (schema-only) dump as a starting point to build your script, if reloading the whole db is undesireable. This will at least save you from having to manually rewrite all your view definitions.

    In the future, unless there's some invariant rule limiting the length of your string, I'd suggest using the TEXT data type to prevent this kind problem.

Posting Permissions

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