check constraint names

[kevin]

For check constraints, we have:
            SELECT has_check( :schema, :table, :description );
            SELECT col_has_check( :schema, :table, :column, :description );
 
In many ways, the col_has_check() covers most of what we need. However, it seems like we should have something like:
            SELECT col_has_named_check( :schema, :table, :check_name, :description );
Or maybe:
            SELECT col_has_named_check( :schema, :table, :column, :check_name, :description );
 
I’m not sure what I fully need :-/ but perhaps this last one as I know the constraint name and the schema/table/column; the point is to ensure a specific check is present so maybe the column doesn't matter. Is there something already existing which I don’t see that does this (with the constraint name) or would I need to give a patch? Or is this too weird and we shouldn’t bother?
 
This query is the basis of the check such that if the output matches the “:check_name” it’s a pass or it doesn’t and fails which make this the “{have}” value:
 
SELECT ccu.constraint_name
FROM information_schema.constraint_column_usage ccu
    JOIN information_schema.check_constraints cc ON ( ccu.constraint_name = cc.constraint_name )
WHERE ccu.table_schema = '$schema' AND ccu.table_name = '$table' AND ccu.column_name = '$column';

The 1 place I could see the above failing is if there is more than 1 check constraint on a column (which strikes me as weird but I don’t think Pg would prohibit it). That means “:check_name” would really have to be :check_names or “array[check_name,…]” to avoid failure for this edge case and then do a set match (array1 @> array2 and array1 <@ array2) unless you can order both sides then I think you could do array1=array2.

To be complete to ensure you've removed a check, I suppose there should also be something like:
            SELECT hasnt_check( :schema, :table, :description );
Example:
            SELECT hasnt_check( ‘myschema’, ‘mytable’, ‘mytable has no CHECK constraints’ );

Thoughts?

Thanks,
Kevin

[David E. Wheeler]

Hi,

> In many ways, the col_has_check() covers most of what we need. However, it seems like we should have something like:
> SELECT col_has_named_check( :schema, :table, :check_name, :description );
> Or maybe:
> SELECT col_has_named_check( :schema, :table, :column, :check_name, :description );
>
> I’m not sure what I fully need :-/ but perhaps this last one as I know the constraint name and the schema/table/column; the point is to ensure a specific check is present so maybe the column doesn't matter. Is there something already existing which I don’t see that does this (with the constraint name) or would I need to give a patch? Or is this too weird and we shouldn’t bother?

It doesn’t exist, but I’d point out that in Postgres CHECKs don’t have names, but constraints do. Thus the other constraint-checking assertions --- for primary keys, foreign keys, unique constraints --- also could use some support for checking by name.

> This query is the basis of the check such that if the output matches the “:check_name” it’s a pass or it doesn’t and fails which make this the “{have}” value:

Look at the pg_constraint table[1] to cover naming for other types of constraints. Maybe something like:

has_constraint(:schema, :table, :name, :type, :description)
has_constraint(:schema, :table, :name, :description)
has_constraint(:table, :name, :description)

And variants for the columns, too. This would avoid overloading the function signatures of the existing functions.

Best,

David

[1]: https://www.postgresql.org/docs/current/catalog-pg-constraint.html

[Logan Grosz]

Just wanted to chime in and say I was looking for this feature last week. I'd use such a thing. I don't want to make any empty promises, but if I find some free time I'll look into implementation.

Logan