Hi all, This problem has also been posted to Stack Overflow. http://stackoverflow.com/questions/19227920/altering-a-parent-table-in-postgresql-8-4-breaks-child-table-defaults
The problem: In Postgresql 8.4 (not tested on other versions), if table `temp_person_two` inherits from`temp_person`, default column values on the child table are ignored if the *parent* table is altered. How to replicate (these don't need to be temporary tables. I made them temporary for your cut-n-paste convenience): First, create table and a child table. The child table should have one column that has a default value. CREATE TEMPORARY TABLE temp_person ( person_id SERIAL, name VARCHAR ); CREATE TEMPORARY TABLE temp_person_two ( has_default character varying(4) DEFAULT 'en'::character varying NOT NULL ) INHERITS (temp_person); Next, create a trigger on the parent table that copies its data to the child table (I know this appears like bad design, but this is a minimal test case to show the problem). CREATE FUNCTION temp_person_insert() RETURNS trigger LANGUAGE plpgsql AS ' BEGIN INSERT INTO temp_person_two VALUES ( NEW.* ); RETURN NULL; END; '; CREATE TRIGGER temp_person_insert_trigger BEFORE INSERT ON temp_person FOR EACH ROW EXECUTE PROCEDURE temp_person_insert(); Then insert data into parent and select data from child. The data should be correct. INSERT INTO temp_person (name) VALUES ('ovid'); SELECT * FROM temp_person_two; person_id | name | has_default -----------+------+------------- 1 | ovid | en (1 row ) Finally, alter the parent table by adding a new, unrelated column. Attempt to insert data and watch a "not-null constraint" violation occur: ALTER TABLE temp_person ADD column foo text; INSERT INTO temp_person(name) VALUES ('Corinna'); ERROR: null value in column "has_default" violates not-null constraint CONTEXT: SQL statement "INSERT INTO temp_person_two VALUES ( $1 .* )" PL/pgSQL function "temp_person_insert" line 2 at SQL statement My version: testing=# select version(); version ------------------------------------------------------------------------------------------------------- PostgreSQL 8.4.17 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit (1 row) Cheers, Ovid -- IT consulting, training, international recruiting http://www.allaroundtheworld.fr/. Buy my book! - http://bit.ly/beginning_perl Live and work overseas - http://www.overseas-exile.com/