?
Solved

Resize varchar column in Postgre

Posted on 2015-01-15
4
Medium Priority
?
402 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 51

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 51

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 51

Accepted Solution

by:
Steve Bink earned 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
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.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

840 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