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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
