I have a skeleton table tb_audit_event that has a bunch of descendant
tables.
I want to remove the pk column from this table and all of the descendant
tables, however issuing an ALTER TABLE tb_audit_event DROP COLUMN
audit_event drops the column only from the parent table.
The docs said that the descendant tables' columns would be removed unless
they had had their own definition for that column. I'm not sure what that
means, but the descendant tables were created using "like tb_audit_event"
to inherit the columns.
Any idea why the descendant columns are not dropping along with the parent?
Here are the table descriptions followed by my table alter and check:
postgres@zeus=>hera:ises=# \d tb_audit_event
Table "public.tb_audit_event"
Column | Type |
Modifiers
----------------+-----------------------------+---------------------------------------------------------
audit_event | integer | not null default
nextval('sq_pk_audit_event'::regclass)
audit_field | integer | not null
row_pk_val | integer | not null
recorded | timestamp without time zone | not null default now()
entity | integer | not null
row_op | character(1) | not null
op_sequence | integer | not null
transaction_id | bigint | not null
process_id | integer | not null
old_value | text |
new_value | text |
Indexes:
"tb_audit_event_pkey" PRIMARY KEY, btree (audit_event)
"tb_audit_event_recorded_key" btree (recorded)
"tb_audit_event_transaction_id_key" btree (transaction_id)
Check constraints:
"tb_audit_event_row_op_check" CHECK (row_op = ANY (ARRAY['I'::bpchar,
'U'::bpchar, 'D'::bpchar]))
Foreign-key constraints:
"tb_audit_event_audit_field_fkey" FOREIGN KEY (audit_field) REFERENCES
tb_audit_field(audit_field)
"tb_audit_event_entity_fkey" FOREIGN KEY (entity) REFERENCES
tb_entity(entity)
Triggers:
tr_redirect_audit_events BEFORE INSERT ON tb_audit_event FOR EACH ROW
EXECUTE PROCEDURE fn_redirect_audit_events()
Number of child tables: 17 (Use \d+ to list them.)
postgres@zeus=>hera:ises=# \d audit_log.tb_audit_event_20120826_0208
Table "audit_log.tb_audit_event_20120826_0208"
Column | Type |
Modifiers
----------------+-----------------------------+---------------------------------------------------------
audit_event | integer | not null default
nextval('sq_pk_audit_event'::regclass)
audit_field | integer | not null
row_pk_val | integer | not null
recorded | timestamp without time zone | not null default now()
entity | integer | not null
row_op | character(1) | not null
op_sequence | integer | not null
transaction_id | bigint | not null
process_id | integer | not null
old_value | text |
new_value | text |
Indexes:
"tb_audit_event_20120826_0208_pkey" PRIMARY KEY, btree (audit_event)
"tb_audit_event_20120826_0208_recorded_idx" btree (recorded)
"tb_audit_event_20120826_0208_transaction_id_idx" btree (transaction_id)
Check constraints:
"tb_audit_event_20120826_0208_recorded_check" CHECK (recorded >=
'2012-08-19 14:57:49.315938'::timestamp without time zone AND recorded <=
'2012-08-26 14:13:04.133753'::timestamp without time zone)
"tb_audit_event_row_op_check" CHECK (row_op = ANY (ARRAY['I'::bpchar,
'U'::bpchar, 'D'::bpchar]))
Inherits: tb_audit_event
postgres@moshe=>devmain:ises=# *alter table tb_audit_event drop column
audit_event;*
ALTER TABLE
postgres@moshe=>devmain:ises=# *\d audit_log.tb_audit_event_20120826_0208*
Table "audit_log.tb_audit_event_20120826_0208"
Column | Type |
Modifiers
----------------+-----------------------------+---------------------------------------------------------
audit_event | integer | not null default
nextval('sq_pk_audit_event'::regclass)
audit_field | integer | not null
row_pk_val | integer | not null
recorded | timestamp without time zone | not null default now()
entity | integer | not null
row_op | character(1) | not null
op_sequence | integer | not null
transaction_id | bigint | not null
process_id | integer | not null
old_value | text |
new_value | text |
Indexes:
"tb_audit_event_20120826_0208_pkey" PRIMARY KEY, btree (audit_event)
"tb_audit_event_20120826_0208_recorded_idx" btree (recorded)
"tb_audit_event_20120826_0208_transaction_id_idx" btree (transaction_id)
Check constraints:
"tb_audit_event_20120826_0208_recorded_check" CHECK (recorded >=
'2012-08-26 14:26:55.761958'::timestamp without time zone AND rec
orded <= '2012-08-26
14:45:35.989979'::timestamp without time zone)
"tb_audit_event_row_op_check" CHECK (row_op = ANY (ARRAY['I'::bpchar,
'U'::bpchar, 'D'::bpchar]))
Inherits: tb_audit_event
Also, another question about the docs. The syntax for the ALTER TABLE
command starts as follows:
ALTER TABLE [ ONLY ] name [ * ]
What is the asterisk? It is not explained anywhere on that page.
Thanks,
Moshe
--
Moshe Jacobson
Nead Werx, Inc. | Senior Systems Engineer
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
mo...@neadwerx.com | www.neadwerx.com
