Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The problem: In Postgresql, if table temp_person_two inherits fromtemp_person, default column values on the child table are ignored if the parent table is altered.

How to replicate:

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 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)
share|improve this question

1 Answer 1

Your problem is that when you add a new column to the_person, its child, the_person_two will have this field appended at the end of columns list (4th position), so after has_default column. See:

db=> \d temp_person
  Column   |       Type        |                            Modifiers                            
-----------+-------------------+-----------------------------------------------------------------
 person_id | integer           | not null default nextval('temp_person_person_id_seq'::regclass)
 name      | character varying | 
 foo       | text              | 

db=> \d temp_person_two 
   Column    |         Type         |                            Modifiers                            
-------------+----------------------+-----------------------------------------------------------------
 person_id   | integer              | not null default nextval('temp_person_person_id_seq'::regclass)
 name        | character varying    | 
 has_default | character varying(4) | not null default 'en'::character varying
 foo         | text                 | 

So, when you execute this:

INSERT INTO temp_person_two VALUES ( NEW.* );

PostgreSQL will actually understand that you want to insert on the first three columns of temp_person_two (as NEW.* will expand to three values), generating something similar to this:

INSERT INTO temp_person_two(person_id,name,has_default)
VALUES ( NEW.person_id, NEW.name, NEW.foo );

So, temp_person_two.has_default will get the value of NEW.foo, which is NULL in your case.

The solution is to simply expand the column names:

INSERT INTO temp_person_two(person_id,name,foo)
VALUES ( NEW.person_id, NEW.name, NEW.foo );

or, you could also use this:

INSERT INTO temp_person_two(person_id,name,foo)
VALUES ( NEW.* );

But this is weak, as any changes on column positions may break your statements, so I'd recommend the first one.

EDIT:

So the conclusion and the lesson learned here is:

Always explicitly type the names of the columns and the values when issuing an INSERT command, in fact, when issuing any SQL command at all... =D

This will save you a lot of time solving problems like that in future.

share|improve this answer
    
Thank you. It's a rather depressing answer because it implies a fair amount of work rewriting our trigger, but it makes sense. –  Curtis Poe Oct 7 '13 at 16:33
    
Sorry for disappointing you... Notice that you could write the insert statements dynamically (using hstore, for instance)... –  MatheusOl Oct 7 '13 at 18:06
1  
@CurtisPoe: it is considered bad programming style when not explicitely stating the columns in an insert statement - with or without inheritance. –  a_horse_with_no_name Oct 7 '13 at 19:12
1  
@CurtisPoe, you could now accept the answer, right?! –  MatheusOl Oct 8 '13 at 14:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.