Key not present in table, but it is.

Version: psql version 9.0, server version 9.1.
PostgreSQL 9.1.3 on x86_64-apple-darwin11.3.0, compiled by
i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc.
build 5658) (LLVM build 2336.1.00), 64-bit


Executed the following statement:

INSERT INTO dia4.veraz(dni,desde) (
    SELECT
        per.dni,
        (now() - (round(random()*100) || ' days')::interval)::date
    FROM
        (SELECT temp_.dni FROM dia4.persona temp_ ORDER BY random()
LIMIT 70 ) per
);

ERROR:  insert or update on table "veraz" violates foreign key
constraint "veraz_dni_fkey"
DETAIL:  Key (dni)=(21530976) is not present in table "persona".


 But the record is present:

coches=# select * from dia4.persona where dni = 21530976;
-[ RECORD 1 ]---------
dni       | 21530976
nombre    | Francisca
apellido  | Calvo
sexo      |
fecha_nac | 1992-08-27
observ    |
salario   | 6885.03


Structure:

coches=# \d+ dia4.veraz
                   Table "dia4.veraz"
   Column    | Type | Modifiers | Storage  | Description
-------------+------+-----------+----------+-------------
 dni         | dni  |           | plain    |
 desde       | date |           | plain    |
 descripcion | text |           | extended |
Indexes:
    "veraz_dni_key" UNIQUE, btree (dni)
Foreign-key constraints:
    "veraz_dni_fkey" FOREIGN KEY (dni) REFERENCES dia4.persona(dni)
Has OIDs: no

coches=# \d+ dia4.persona
                     Table "dia4.persona"
  Column   |     Type     | Modifiers | Storage  | Description
-----------+--------------+-----------+----------+-------------
 dni       | dni          | not null  | plain    |
 nombre    | text         | not null  | extended |
 apellido  | text         | not null  | extended |
 sexo      | sexo         |           | extended |
 fecha_nac | date         |           | plain    |
 observ    | text         |           | extended |
 salario   | numeric(8,2) |           | main     |
Indexes:
    "persona_pkey" PRIMARY KEY, btree (dni)
Check constraints:
    "persona_fecha_nac_check" CHECK (fecha_nac < now()::date)
Referenced by:
    TABLE "dia4.veraz" CONSTRAINT "veraz_dni_fkey" FOREIGN KEY (dni)
REFERENCES dia4.persona(dni)
Triggers:
    part_maestra BEFORE INSERT ON dia4.persona FOR EACH ROW EXECUTE
PROCEDURE part()
    t_actualiza_sexo BEFORE INSERT OR UPDATE ON dia4.persona FOR EACH
ROW EXECUTE PROCEDURE actualiza_sexo()
Child tables: dia4.persona_200,
              dia4.persona_201,
              dia4.persona_202,
              dia4.persona_203,
              dia4.persona_204,
              dia4.persona_205,
              dia4.persona_206,
              dia4.persona_207,
              dia4.persona_208,
              dia4.persona_209,
              dia4.persona_210,
              dia4.persona_211,
              dia4.persona_212,
              dia4.persona_213,
              dia4.persona_214,
              dia4.persona_215,
              dia4.persona_216,
              dia4.persona_217,
              dia4.persona_218,
              dia4.persona_219,
              dia4.persona_220
Has OIDs: no


CREATE DOMAIN dni  AS bigint  CHECK (VALUE > 0);

coches=# show constraint_exclusion;
 constraint_exclusion
----------------------
 partition
(1 row)


Tried to insert without selecting random, and fails in the first one:

coches=# INSERT INTO dia4.veraz(dni,desde) (
       SELECT
   per.dni::dni,
    (now() - (round(random()*100) || ' days')::interval)::date
  FROM
    (SELECT  temp_.dni FROM dia4.persona temp_ LIMIT 10  ) per     );

ERROR:  insert or update on table "veraz" violates foreign key
constraint "veraz_dni_fkey"
DETAIL:  Key (dni)=(20000000) is not present in table "persona".

coches=# select * from dia4.persona where dni =20000000::dni;
 dni    | nombre | apellido | sexo | fecha_nac  | observ | salario
 ----------+--------+----------+------+------------+--------+---------
20000000 | Nayara | Calvo    |      | 1990-12-10 |        | 7457.62(1 row)

coches=# select * from dia4.persona where dni =20000000;
   dni    | nombre | apellido | sexo | fecha_nac  | observ | salario
----------+--------+----------+------+------------+--------+---------
 20000000 | Nayara | Calvo    |      | 1990-12-10 |        | 7457.62
(1 row)



create table just_check as (SELECT
   per.dni::dni,
    (now() - (round(random()*100) || ' days')::interval)::date
  FROM
    (SELECT  temp_.dni FROM dia4.persona temp_ order by random() LIMIT
30  ) per  );

coches=# select * from dia4.persona where dni IN (select dni from
just_check);
   dni    |  nombre   | apellido  | sexo | fecha_nac  | observ | salario
----------+-----------+-----------+------+------------+--------+---------
 20021904 | Romina    | Guevara   |      | 1993-02-10 |        | 2333.28
 20943908 | Ricardo   | Tucci     |      | 1995-03-31 |        | 9514.51
 21351357 | Horacio   | Calvo     |      | 1990-09-11 |        | 7074.95
 21464916 | Silvia    | Cantina   |      | 1984-11-14 |        | 2686.13
 21254140 | Silvia    | Casas     |      | 1990-04-20 |        | 6573.23
 20539333 | Noa       | Fernandez |      | 1986-08-23 |        | 5037.20
 20615338 | Ricardo   | Samuel    |      | 1989-12-19 |        | 4942.00
 21406933 | Noa       | Cantina   |      | 1995-08-18 |        | 7695.94
 20251864 | Noa       | Ortigoza  |      | 1993-09-01 |        | 3682.97
 20939525 | Ricardo   | Ike       |      | 1996-01-09 |        | 7403.78
 20120439 | Mario     | Rodriguez |      | 1992-03-15 |        | 4258.52
 21341054 | Ana       | Guevara   |      | 1983-03-20 |        | 5835.69
 21822395 | Romina    | Fernandez |      | 1992-05-30 |        | 3362.66
 20489612 | Tiziana   | Casas     |      | 1995-05-30 |        | 2436.46
 21383564 | Tiziana   | Tucci     |      | 1985-12-31 |        | 4930.83
 21349995 | Fabricio  | Ike       |      | 1988-04-03 |        |  768.36
 21818746 | Mario     | Baldo     |      | 1994-01-19 |        | 3863.44
 21439843 | Tiziana   | Calvo     |      | 1991-10-26 |        | 2441.85
 20661755 | Fabricio  | Tucci     |      | 1984-06-22 |        | 3198.75
 20779114 | Romina    | Guevara   |      | 1995-09-05 |        | 2881.89
 21049807 | Tiziana   | Vespi     |      | 1994-12-28 |        | 5877.20
 20547171 | Ricardo   | Baldo     |      | 1986-10-18 |        | 5505.65
 21501282 | Horacio   | Fernandez |      | 1983-03-23 |        | 1189.81
 20881018 | Silvia    | Baldo     |      | 1983-01-30 |        | 4890.37
 21313717 | Romina    | Vespi     |      | 1991-09-05 |        | 4139.56
 20791388 | Priscila  | Smith     |      | 1986-12-15 |        | 5408.78
 21419663 | Mario     | Ike       |      | 1984-07-23 |        | 5306.78
 20600455 | Tiziana   | Cantina   |      | 1984-01-01 |        | 3271.07
 20001401 | Mario     | Ike       |      | 1984-02-15 |        | 6912.50
 21867811 | Francisca | Ike       |      | 1995-03-29 |        | 1147.19
(30 rows)

coches=# insert into dia4.veraz (dni,desde) select dni, (now() -
(round(random()*100) || ' days')::interval)::date from just_check;
ERROR:  insert or update on table "veraz" violates foreign key
constraint "veraz_dni_fkey"
DETAIL:  Key (dni)=(21349995) is not present in table "persona".


The script is in github at:
https://github.com/3manuek/Random-database-for-Postgres

I tried with other versions >9.0 and is the same failure.

--
--
Emanuel Calvo