Hi again, one, possibly, last, thing. I wrote: > I still find the CHECK constraint > to be a more natural way to express what I want, though. Now let me extend on this a bit. The CHECK constraint says nicely and natively, what constraints (no pun intended) I want the data to fulfil. With both the CHECK constraint and the trigger, we need an equivalent constraint on the referenced foreign table, which we have, which we always had; in the schema example I gave, this is not allowing the field “standalone” to change. I can, however, VALIDATE a CHECK constraint after the dump has been restored; I cannot do that with a trigger (or I haven’t found out how to do it). This means that if a user manually edited the dump prior to restoring I have no way to make the restoring transaction fail if the data is bogus. Sure, SOL on the user, but I created the various CHECK constraints to cover against user errors in the first place. I would very much prefer for PostgreSQL to ① formally allow and support such CHECK constraints, ② add an ALTER TABLE … INVALIDATE CONSTRAINT command, to pair with the existing ALTER TABLE … VALIDATE CONSTRAINT command, and ③ hack pg_dump to invalidate constraints before and revalidate them after the fact. This would allow me to express what I want in a more natural and easier to validate (pun intended this time) way. It feels “right” to use a trigger on the referenced table preventing the field from changing, but it feels more right for the referencing table to simply use a CHECK constraint. As for validation, see above. For my current use case, the ship has sailed, but (especially given that such CHECK constrains are currently, while not officially supported, at least “tolerated” and (except in pg_dump) work) this is something to consider for PostgreSQL 10 in my opinion. Thank you for listening. bye, //mirabilos -- tarent solutions GmbH Rochusstraße 2-4, D-53123 Bonn • http://www.tarent.de/ Tel: +49 228 54881-393 • Fax: +49 228 54881-235 HRB 5168 (AG Bonn) • USt-ID (VAT): DE122264941 Geschäftsführer: Dr. Stefan Barth, Kai Ebenrett, Boris Esser, Alexander Steeg -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
