| Mike,
Doesn't look like it, at least on 8.4. Give the script below a try for yourself.
Another approach would be to create a new table with the schema you need, insert the rows from your existing table into it, rename the two tables appropriately, then recreate the indexes and trigger on the new table. That way you won't have to worry about the trigger firing at all.
Bob Lunney
==============
create table blah (blah int, ts timestamptz);
create function
update_timestamp() returns trigger as $$ begin new.ts = now(); return new; end; $$ language plpgsql;
create trigger blah_t before insert or update on blah for each row execute procedure update_timestamp();
insert into blah values (1); insert into blah values (2); insert into blah values (3); select * from blah;
blah | ts ------+------------------------------- 1 | 2010-06-21 14:33:32.14576-04 2 | 2010-06-21 14:33:34.545739-04 3 | 2010-06-21 14:33:36.097878-04 (3 rows)
alter table blah alter column blah type bigint; select *
from blah;
blah | ts ------+------------------------------- 1 | 2010-06-21 14:33:32.14576-04 2 | 2010-06-21 14:33:34.545739-04 3 | 2010-06-21 14:33:36.097878-04 (3 rows)
===============
--- On Mon, 6/21/10, Mike Broers <mbroers@xxxxxxxxx> wrote:
From: Mike Broers <mbroers@xxxxxxxxx>
Subject: alter column resize triggers question
To: pgsql-admin@xxxxxxxxxxxxxx
Date: Monday, June 21, 2010, 2:18 PM
Pg v8.3.8
I have a table whose column size needs to be increased:
\d
dim_product
Table "report.dim_product"
Column | Type |
Modifiers
----------------------+--------------------------+--------------------------------------------------------------------------------------
product_id | integer | not null default nextval('dim_product_id_seq'::regclass)
application_id | integer | not null
source_product_cd | integer | not null
product_type | character varying(20) | not null
product_name | character varying(100) | not null
vendor_offer_cd | character varying(30) |
service_name | character varying(20) |
category | character varying(40) |
svc_line_cd | character varying(40) |
established | timestamp with time zone | not null
modified | timestamp with time zone | not null
Indexes:
"dim_product_pkey" PRIMARY KEY, btree (product_id)
"idx_dim_product_modified" btree (modified)
"idx_dim_product_source_product_cd" btree (source_product_cd)
Triggers:
t_dim_product_timestamp_b_iu BEFORE INSERT OR UPDATE ON dim_product FOR EACH ROW EXECUTE PROCEDURE public.update_timestamps()
I need to change service_name column to varchar(55), my plan was to backup the table with pg_dump, then run the below alter statement:
alter table dim_product alter column service_name type varchar(55);
But i am worried about the triggers because I believe that the alter table statement will rewrite the table and I dont want those triggers firing. Does anyone know if I need to disable these triggers prior to the alter table statement, or if there are any other dependencies or precautions I should review before attempting this action? I have also seen there is a workaround with running updates to the pg_attribute table but frankly that makes me a little nervous.
Thanks in advance,
Mike
| 