Log or notice values or rows that cause a constraint violation

Hi all

I'm once again trying to figure out which row of a 5000-record insert is
violating a constraint, and can't help thinking how nice it'd be if Pg
would report the contents of the row violating the constraint, or at
least the values that were tested by the constraint check.

It's really, really frustrating to track down constraint violations
without this; I usually land up using a trigger to RAISE NOTICE each row
so I can see where the import dies, or I wrap the key field of an INSERT
... SELECT in a polymorphic identity function that does a RAISE NOTICE
of the input as a side-effect. Needless to say, an improvement would be
nice here, as both of these are ugly hacks.

I keep on hoping that the violating data is sent in the error detail,
but \set verbosity verbose in psql doesn't reveal the goods, and the
problem row data isn't recorded in the log.

Given this command sequence:

create table test ( x integer not null, check(x > 0) );
insert into test(x) values (1),(2),(3),(-1),(-2),(3);

I'd like to see more than:

ERROR:  new row for relation "test" violates check constraint "test_x_check"

specifically something like:

ERROR:  new row for relation "test" violates check constraint "test_x_check"
DETAIL: violating row was (-2)

I know you can log statement params, but that's no use if your violators
are part of a long COPY, a multi-insert, an INSERT ... SELECT, etc.

Have I missed something blindingly obvious in the docs and never noticed
it in a couple of years of using Pg, or is there no way to get Pg to log
and report this data at the moment?

--
Craig Ringer

POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088     Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/