• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 561
  • Last Modified:

Resize varchar column in Postgre

Hello,

I have a Postgre 9 database with some data in it. One of it's tables is set to character varying(15) and I need to expand it. Some friends told me that if I do this, I would have to change all the views that use that table (there are A LOT)... Is that correct? What is the best way to do it?


Thanks in advance!
0
Paulo Ventura
Asked:
Paulo Ventura
  • 3
1 Solution
 
Steve BinkCommented:
AFAIK, changing the structure of a table does not necessarily call for rebuilding a view dependent on that table.  That would only follow if you removed a field the view needed, changed a field name, etc.  Otherwise the VIEW is more like a saved SELECT statement.
0
 
Paulo VenturaAuthor Commented:
Hello,

I've tried running the statement below, but returned an error.

Thanks in advance!

Statement:
ALTER TABLE produtos ALTER COLUMN cod_produto TYPE varchar(25);

Error:
ERROR: cannot alter type of a column used by a view or rule
SQL state: 0A000
Detail: rule _RETURN on view vw_produtos_producao_geral depends on column "cod_produto"
0
 
Steve BinkCommented:
My apologies, but I was wholly incorrect.  Further research shows that it is necessary to drop the views in order to change a column width.

There was one other option suggesting you alter the catalog to reflect the new width, but I would hesitate to recommend this.  Changing the system catalogs is a dangerous thing.
0
 
Steve BinkCommented:
See how to alter a postgres table without deleting the view for more information.  Be sure to read all the comments there.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now