Solved

Resize varchar column in Postgre

Posted on 2015-01-15
4
193 Views
Last Modified: 2015-01-26
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
Comment
Question by:Paulo Ventura
  • 3
4 Comments
 
LVL 50

Expert Comment

by:Steve Bink
ID: 40554011
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
 

Author Comment

by:Paulo Ventura
ID: 40557466
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
 
LVL 50

Expert Comment

by:Steve Bink
ID: 40558355
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
 
LVL 50

Accepted Solution

by:
Steve Bink earned 500 total points
ID: 40558358
See how to alter a postgres table without deleting the view for more information.  Be sure to read all the comments there.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now