Re: Changing varchar length by manipulating pg_attribute

On 1/13/16 5:59 AM, Christian Ramseyer wrote:
> UPDATE pg_attribute SET atttypmod = 35+4   -- new desired length + 4
> WHERE attrelid = 'TABLE1'::regclass
> AND attname = 'COL1';

I don't know of any reason that wouldn't work. Note that you might have
to make the same change to all the views too.

> Is this safe to do in Postgres 9.4? Also, best practice seems to be to
> use text nowadays, is there even a variant of this that lets me convert

FWIW, I prefer using varchar with a fairly large limit unless the field
really does need to be unlimited. That protects against bad code or a
malicious user filling your database with garbage.

> a "column from character varying(256)" to "text" without having to
> recreate all the nested views?

You could probably change pg_attribute.atttypid to 'text'::regtype. You
should change atttypmod to -1 at the same time if you do that.

Obviously you should test all of this thoroughly before doing it in
production.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com