title: Error:"Key ... is not present in table"
body: <p>I have a table with a character varying(12) field in it which is its PRIMARY KEY.  I ran this query</p>
  
  <pre><code>SELECT * FROM bg WHERE bg_id ='470370111002'
  </code></pre>
  
  <p>It selects a row from the table.  All looks good.  Then I try.</p>
  
  <pre><code>INSERT INTO csapp_center_bgs(bg_id,center_id) VALUES('470370111002',2)
  </code></pre>
  
  <p>There is a foreign key on bg_id that looks like...</p>
  
  <pre><code>ALTER TABLE csapp_center_bgs
  ADD CONSTRAINT csapp_center_bgs_bg_id_65c818f360c84dc5_fk_bg_bg_id
  FOREIGN KEY (bg_id)
  REFERENCES tiger.bg (bg_id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED;
  </code></pre>
  
  <p>Here is the precise error...</p>
  
  <pre><code>    ERROR:  insert or update on table "csapp_center_bgs" violates foreign key constraint "csapp_center_bgs_bg_id_65c818f360c84dc5_fk_bg_bg_id"
  DETAIL:  Key (bg_id)=(470370111002) is not present in table "bg".
  ********** Error **********
  
  ERROR: insert or update on table "csapp_center_bgs" violates foreign key constraint "csapp_center_bgs_bg_id_65c818f360c84dc5_fk_bg_bg_id"
  SQL state: 23503
  Detail: Key (bg_id)=(470370111002) is not present in table "bg".
  </code></pre>
  
  <p>Why did this not work?!  Any ideas?  Here is \d+ bg...</p>
  
  <pre><code>  Column  |         Type          |                    Modifiers                     | Storage  | Stats target | Description 
  ----------+-----------------------+--------------------------------------------------+----------+--------------+-------------
   gid      | integer               | not null default nextval('bg_gid_seq'::regclass) | plain    |              | 
   statefp  | character varying(2)  |                                                  | extended |              | 
   countyfp | character varying(3)  |                                                  | extended |              | 
   tractce  | character varying(6)  |                                                  | extended |              | 
   blkgrpce | character varying(1)  |                                                  | extended |              | 
   bg_id    | character varying(12) | not null                                         | extended |              | 
   namelsad | character varying(13) |                                                  | extended |              | 
   mtfcc    | character varying(5)  |                                                  | extended |              | 
   funcstat | character varying(1)  |                                                  | extended |              | 
   aland    | double precision      |                                                  | plain    |              | 
   awater   | double precision      |                                                  | plain    |              | 
   intptlat | character varying(11) |                                                  | extended |              | 
   intptlon | character varying(12) |                                                  | extended |              | 
   the_geom | geometry              |                                                  | main     |              | 
  Indexes:
      "bg_pkey" PRIMARY KEY, btree (bg_id)
      "idx_bg_geom" gist (the_geom) CLUSTER
  Check constraints:
      "enforce_dims_geom" CHECK (st_ndims(the_geom) = 2)
      "enforce_geotype_geom" CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL)
      "enforce_srid_geom" CHECK (st_srid(the_geom) = 4269)
  Referenced by:
      TABLE "csapp_center_bgs" CONSTRAINT "csapp_center_bgs_bg_id_65c818f360c84dc5_fk_bg_bg_id" FOREIGN KEY (bg_id) REFERENCES bg(bg_id) DEFERRABLE INITIALLY DEFERRED
  Child tables: tiger_data.tn_bg
  Has OIDs: no
  </code></pre>
  
  <p>And here is \d+ on csapp_...</p>
  
  <pre><code>  Column   |         Type          |                           Modifiers                           | Storage  | Stats target | Description 
  -----------+-----------------------+---------------------------------------------------------------+----------+--------------+-------------
   id        | integer               | not null default nextval('csapp_center_bgs_id_seq'::regclass) | plain    |              | 
   bg_id     | character varying(12) | not null                                                      | extended |              | 
   center_id | integer               | not null                                                      | plain    |              | 
  Indexes:
      "csapp_center_bgs_pkey" PRIMARY KEY, btree (id)
      "csapp_center_bgs_5e94e25f" btree (bg_id)
      "csapp_center_bgs_c63f1184" btree (center_id)
  Foreign-key constraints:
      "csapp_center_bgs_bg_id_65c818f360c84dc5_fk_bg_bg_id" FOREIGN KEY (bg_id) REFERENCES bg(bg_id) DEFERRABLE INITIALLY DEFERRED
      "csapp_center_bgs_center_id_360e6806f7d3fee_fk_csapp_centers_id" FOREIGN KEY (center_id) REFERENCES csapp_centers(id) DEFERRABLE INITIALLY DEFERRED
  Has OIDs: no
  </code></pre>
  
  <p>Here is the version:</p>
  
  <pre><code>                                               version                                                
  ------------------------------------------------------------------------------------------------------
   PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit
  </code></pre>
  
  <p>And here is my search path....</p>
  
  <pre><code>search_path  
  ---------------
   public, tiger
  (1 row)
  </code></pre>
  
  <p>bg is in schema tiger and csapp_center_bgs is in schema public...</p>