Solved

Resize varchar column in Postgre

Posted on 2015-01-15
4
202 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
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…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
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…

816 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

11 Experts available now in Live!

Get 1:1 Help Now