Re: ALTER TABLE ... ADD FOREIGN KEY ... NOT ENFORCED

On Tue, 2011-01-18 at 14:26 -0600, Jim Nasby wrote:
> >
> > 2 sub-command changes:
> >
> > ALTER TABLE foo ADD FOREIGN KEY fkoo ... NOT VALID;
> >
> > ALTER TABLE foo VALIDATE CONSTRAINT fkoo;
>
> Sorry for the late reply; I just saw this...
>
> Is there any way to be able to get the bad records out of the ALTER ... VALIDATE? I know it's pretty unusual, but for a set of large tables, it could take hours to run a separate query that gives you the results.
>
> BTW, I agree that this should be a DDL command, it would be very odd if it wasn't. But I also see it being very useful to be able to get the set of bad rows at the same time. Maybe if there was an SRF that did the real work and the ALTER just ignored the resultset?

I agree. Unfortunately that wasn't the consensus when I proposed that
earlier, so its just a simple validation true/false.

I could add an SRF that ran the validation query but brought back the
rows, but if zero then it sets valid. We could have both...

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services