Changing varchar length by manipulating pg_attribute
Hi
I have a database in which I'd like to increase the length of a varchar
column. Unfortunately, the column is used in various views which then
are used in other views, so doing this with ALTER TABLE ALTER COLUMN
TYPE is quite a lot of work.
I have found this suggestion
<http://sniptools.com/databases/resize-a-column-in-a-postgresql-table-without-changing-data>
to just update pg_attribute like this:
UPDATE pg_attribute SET atttypmod = 35+4 -- new desired length + 4
WHERE attrelid = 'TABLE1'::regclass
AND attname = 'COL1';
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
a "column from character varying(256)" to "text" without having to
recreate all the nested views?
Thanks
Christian
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general