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_tbefore 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 <mbro...@gmail.com> wrote:
From: Mike Broers <mbro...@gmail.com>
Subject: [ADMIN] alter column resize triggers question
To: pgsql-admin@postgresql.org
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
