CODEWITHSUNDEEP
pythonsqlpostgresql
Lawrence Liu
Lawrence Liu

Reputation: 65

How to increase postgre varchar length without rewrite the table

Im trying to increase varchar length at my table, the current type is varchar(10). i need to update to varchar(20). how can i do it safely? i tried this:

alter table listing alter column city_name type character varying(20);

however, after i exec it, i saw this:

An error occurred when executing the SQL command:
alter table listing alter column market type character varying(20)

ERROR: cannot alter type of a column used by a view or rule
  Detail: rule _RETURN on view listing_with_zip_v depends on column "city_name"
1 statement failed.

do i need to rewrite it?

Upvotes: 1

Views: 1492

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247545

You will have to drop the view, run the ALTER TABLE, then create the view again. And, in anticipation of your follow-up question, there is no other way. We think it is great like that, because we like consistence.

Anyway, to make up for the trouble, PostgreSQL allows you to run all three statements in a single transactions, so that there can be no race conditions with concurrent queries.

My recommendation is to change the data type to text, otherwise you are running the risk of having to go through the same ordeal again later.

Upvotes: 1

Laxman Kumar
Laxman Kumar

Reputation: 154

You can use below syntax for changing the datatype of a column

ALTER TABLE table_name ALTER COLUMN column_name TYPE varchar(10)

alter table listing alter column city_name type varchar(20);

Upvotes: 1

Related Questions