Sample Header Ad - 728x90

Retrieve Names of Check Constraints Specific to a Column

2 votes
1 answer
1290 views
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.
Asked by Avocado (245 rep)
Apr 5, 2021, 06:56 PM
Last activity: Apr 6, 2021, 02:38 AM