How to avoid identifier truncation in Postgres

Question

I would like to drop all foreign keys associated to a table.

I first identify the foreign keys associated to it using the below

  SELECT DISTINCT constraint_name
  FROM information_schema.key_column_usage
  WHERE table_name = 'crm_campaign_offer_customer_groups'
  AND   table_schema = 'schema001'
  AND   constraint_name LIKE '%fkey%'

Then loop through each of these deleting the foreign keys using a statement like

ALTER TABLE crm_campaign_offer_customer_groups DROP CONSTRAINT crm_campaign_offer_customer_groups_variable_1_fkey1;

The issue that is occurring is that it first truncates the foreign key expression then tries to drop the truncated expression

NOTICE: identifier "..." will be truncated to "..."
ERROR: constraint "..." of relation "..." does not exist

It seems that it is truncating identifiers > 63 characters, but I'm hoping there is an alternative as the table and variable naming conventions are already set

Answer 1

For those that ended up here, check your key names closely. Postgres will have also pre-truncated the generated name on creation, so you will need to match this truncation on DROP to reference it correctly.

ALTER TABLE really_long_table_more_than_63_chars ADD PRIMARY KEY (fields);

->

ALTER TABLE really_long_table_more_than_63_chars DROP CONSTRAINT 
really_long_table_more_than_63_c_pkey;

Postgres behavior, when creating a constraint or index without specifying a name, appears to be:

Generate name as {table_name} + _pkey or {table_name} + _check

If above name.length is > 63 chars, truncate {table_name} short enough such that combined string is 63. i.e {table_name}[:58] + _pkey or {table_name}[:57] + _check