Retrieve Names of Check Constraints Specific to a Column

Question

PostgreSQL 12

When I rename a column in a table, I would like to dynamically rename the matching check constraints as well.

I have a hypothetical table:

CREATE TABLE hypothetical_table (
 id SERIAL PRIMARY KEY,
 some_col text,
 some_col_also text
);

There are constraints:

ALTER TABLE hypothetical_table ADD CONSTRAINT some_col_length_>_5 CHECK(char_length(some_col) > 5);
ALTER TABLE hypothetical_table ADD CONSTRAINT some_col_also_length_>_5 CHECK(char_length(some_col_also) > 5);

I do not know ahead of time what constraints a column may have. I do know that they are prefixed with the column name. But, like some_col constraint above, that prefix may match another column's (some_col_also).

When I rename a column in this table, I would like to rename the constraints as well, to match the new column name.

So, if I were to rename some_col, how can I select all of the check constraints associated with that column?

I have tried joining pg_catalog.pg_constraint with pg_catalog.pg_attribute ON con.conkey = pga.attnum (with conkey unnested), but that returns many constraints that are not associated with that column.

Answer 1

how can I select all of the check constraints associated with that column?

pg_constraint.conkey is an array column, so we join on a.attnum = ANY(c.conkey) and the oid of the relation additionally. Like:

SELECT conname -- pg_get_constraintdef(oid), *
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid -- !
 AND a.attnum = ANY(c.conkey) -- !
WHERE c.conrelid = 'hypothetical_table'::regclass
AND c.contype = 'c' -- c = check constraint
AND a.attname = 'some_col';

But I am doubtful of your overall objective:

When I rename a column in a table, I would like to dynamically rename the matching check constraints as well.

CHECK constraints are always table constraints in Postgres and can reference any number of table columns. Even when formulated as column constraints! The manual:

Column Check Constraints

The SQL standard says that CHECK column constraints can only refer to the column they apply to; only CHECK table constraints can refer to multiple columns. PostgreSQL does not enforce this restriction; it treats column and table check constraints alike.

So some_col and some_col_also can be referenced at the same time (multiple times). And both can be in the constraint name. I don't see how a completely dynamic solution can be bullet-proof, unless you can rely on a strict naming convention. (Can you really?). Is the following a reference to some_col or some_col_also?

some_col_also_is_a_fruit -- oh, wait!

Rewriting the constraint expression itself also demands attention.

• Avocado: Thank you! some_col_also_is_a_fruit .... sweat ---- minor note, in my q i say "(with conkey unnested)" which is why I didn't get an err when joining to attnum. Is there a better way to phrase that?
• Erwin Brandstetter: @Avocado: Oh right, the unnested part slipped my attention. Scratching that bit.