PostgreSQL – Constrain Array Values to Allowed Set

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.

A couple of things you might want to consider:

1. Maintenance of the data.

   If the data is going to change often then it would be easier and quicker to have the data normalised so you only have to change it in one place and have all the usages of it automatically update. Conversely, if the data hardly ever changes then this is not a consideration.

2. Full text searching.

   This should be quicker than doing like '%string%' searches and, depending on your database and what's available, may dictate the format you have store your data in.

   In addition there's the obvious thing that storing the text repeatedly in the table(s) will increase the size of your database. This may have an impact on performance or cost you more to make sure you have enough disk space and/or memory available on your server.

   Wikipedia has an article on the basics. It's not an area I'm terribly familiar with, just that a colleague is looking into this and there are trade-offs with how the data is organised when doing other searches. Other links - MSDN, MySQL and PostgreSQL