Forum OpenACS Q&A: resizing (var)char columns in pg

Collapse
1: resizing (var)char columns in pg
Posted by Jonathan Ellis on 06/20/02 05:37 PM
it took me forever to find this, so in hopes it helps someone else, all you need to do is this: 
update pg_attribute set atttypmod = desired_length + 4
where attrelid = (select oid from pg_class where relname = 'mytable')
  and attname = 'mycolumn';
You can use this to make smaller as well as larger -- I wanted usernames to have a shorter max than the default of 100 but doing a reload is quite painful on a 300MB (uncompressed) dump. This did the trick for me; I've since used it to increase the faq answer length as well.
Collapse
2: Response to resizing (var)char columns in pg (response to 1)
Posted by Don Baccus on 06/21/02 09:13 AM
This is great!  Does anyone know how to do this in Oracle?
Collapse
3: Response to resizing (var)char columns in pg (response to 1)
Posted by Sebastiano Pilla on 06/21/02 09:26 AM
Not sure if I understand the issue correctly, but for Oracle 8i have a look at ALTER TABLE mytable MODIFY mycolumn .... There are some restrictions, see Technet for details. For Oracle 9i there is a DBMS_REDEFINITION package that's worth looking into.
Collapse
4: Response to resizing (var)char columns in pg (response to 1)
Posted by Andrei Popov on 06/21/02 12:39 PM
I also suppose that one could define a function like: 
create function varchar_resize (
  varchar(100),
  varchar(100),
  integer
) returns integer as '
declare
  p_table_name      alias for $1;
  p_col_name        alias for $2;
  p_desired_length  alias for $3;
  v_dummy           integer;
begin
  update pg_attribute
  set atttypmod = p_desired_length + 4
  where attrelid = (select oid 
                    from pg_class 
                    where relname = p_table_name)
        and attname = p_col_name;
  return 0;
end;
' language 'plpgsql';