Problem with Postgres ALTER TABLE

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.)

Now \d foo shows:

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

Bonus: that was waaay faster than doing:

ALTER TABLE foo ALTER COLUMN names TYPE varchar(20);

Technically you can change the size of the table column without changing the size of the view column, but no guarantees on what side effects that will have; it's probably best to change them both at once.

source and fuller explanation: http://sniptools.com/databases/resize-a-column-in-a-postgresql-table-without-changing-data

I wanted to comment on the second answer but cannot since I'm too new to stackoverflow, so here my comment: To those interested in the original article mentioned in that answer, the blogspot entry is not available any more but the wayback machine has it still stored: https://web.archive.org/web/20180323155900/http://mwenus.blogspot.com/2014/04/postgresql-how-to-handle-table-and-view.html Here is the article itself in case archive.org should be turned off at some future point in time: 2014-04-22 PostgreSQL: How to handle table and view dependencies PostgreSQL is very restrictive when it comes to modyfing existing objects. Very often when you try to ALTER TABLE or REPLACE VIEW it tells you that you cannot do it, because there's another object (typically a view or materialized view), which depends on the one you want to modify. It seems that the only solution is to DROP dependent objects, make desired changes to the target object and then recreate dropped objects.

It is tedious and cumbersome, because those dependent objects can have further dependencies, which also may have other dependencies and so on. I created utility functions which can help in such situations.

The usage is very simple - you just have to call: select deps_save_and_drop_dependencies(p_schema_name, p_object_name); You have to pass two arguments: the name of the schema and the name of the object in that schema. This object can be a table, a view or a materialized view. The function will drop all views and materialized views dependent on p_schema_name.p_object_name and save DDL which restores them in a helper table.

When you want to restore those dropped objects (for example when you are done modyfing p_schema_name.p_object_name), you just need to make another simple call: select deps_restore_dependencies(p_schema_name, p_object_name); and the dropped objects will be recreated.

These functions take care about: dependencies hierarchy proper order of dropping and creating views/materialized views across hierarchy restoring comments and grants on views/materialized views Click here for a working sqlfiddle example or check this gist for a complete source code.

Autor: Mateusz Wenus o 19:32

do $$            
  declare gorev_lisans_ihlali_def text;
  declare exec_text text;
begin          
  gorev_lisans_ihlali_def := pg_get_viewdef('public.gorev_lisans_ihlali');
  drop view public.gorev_lisans_ihlali;

    
  exec_text := format('create view public.gorev_lisans_ihlali as %s', 
     gorev_lisans_ihlali_def);
      ALTER TABLE public.ara_bakis_duyma
        ALTER COLUMN gain TYPE   DOUBLE PRECISION;
  execute exec_text;
end $$;