?
Solved

Resize varchar column in Postgre

Posted on 2015-01-15
4
Medium Priority
?
323 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
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 Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

752 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