Hi,
I've would find it useful if check constraints and unique constraints
would give a value which is violating the constraint.
Foreign keys give a value that is failing for the foreign key, is there
a reason that other constraints don't do the same thing?
example psql session from 8.3beta4;
# create table test (x integer ,primary key (x));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"test_pkey" for table "test"
CREATE TABLE
# insert into test values (1);
INSERT 0 1
# insert into test values (1);
ERROR: duplicate key value violates unique constraint "test_pkey"
STATEMENT: insert into test values (1);
# create table test2 (y integer references test(x));
CREATE TABLE
# insert into test2 values (2);
ERROR: insert or update on table "test2" violates foreign key
constraint "test2_y_fkey"
DETAIL: Key (y)=(2) is not present in table "test".
STATEMENT: insert into test2 values (2);
# create table test3 (z integer check (z>0));
CREATE TABLE
# insert into test3 values (-1);
ERROR: new row for relation "test3" violates check constraint
"test3_z_check"
STATEMENT: insert into test3 values (-1);
# insert into test3 select g from generate_series(-1,1) as g;
ERROR: new row for relation "test3" violates check constraint
"test3_z_check"
STATEMENT: insert into test3 select g from generate_series(-1,1) as g;
# insert into test2 select g from generate_series(-1,1) as g;
ERROR: insert or update on table "test2" violates foreign key
constraint "test2_y_fkey"
DETAIL: Key (y)=(-1) is not present in table "test".
STATEMENT: insert into test2 select g from generate_series(-1,1) as g;
Notice that the foreign key case always reports the value that is
violating. None of the other cases do. If all cases could report the
error it would assist greatly in bulk load/INSERT INTO SELECT type queries.
Is this possible or difficult? or has nobody had the inclination?
Thanks
Russell Smith