Alter table change size of the varchar column without drop view

Ini nih cara ngubah length dari varchar, biasa nya tidak bisa langsung alter biasa karena ada dependent ke view.

If you don’t need to change the type of the field, but just the size of it, this approach should work:

Starting with these tables:

CREATE TABLE foo (id integer primary key, names varchar(10));
CREATE VIEW voo AS (SELECT id, names FROM foo);

\d foo and \d voo both show the length as 10:

id     | integer               | not null
names  | character varying(10) | 

Now change the lengths to 20 in the pg_attribute table:

UPDATE pg_attribute SET atttypmod = 20+4
WHERE attrelid IN ('foo'::regclass, 'voo'::regclass)
AND attname = 'names';

(note: the 20+4 is some crazy postgresql legacy thing, the +4 is compulsory.)


Repost from:

Performance Tuning PostgreSQL


PostgreSQL is the most advanced and flexible Open Source SQL database today. With this power and flexibility comes a problem. How do the PostgreSQL developers tune the default configuration for everyone? Unfortunately the answer is they can’t. The problem is that every database is not only different in its design, but also its requirements. Some systems are used to log mountains of data that is almost never queried. Others have essentially static data that is queried constantly, sometimes feverishly. Most systems however have some, usually unequal, level of reads and writes to the database. Add this little complexity on top of your totally unique table structure, data, and hardware configuration and hopefully you begin to see why tuning can be difficult. Continue reading