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

Please provide a real name that exceeds 63 chars in your question. crm_campaign_offer_customer_groups_variable_1_fkey1 is only 51... you seem to have built postgres with NAMEDATALEN more the default and then upgraded it?.. Otherwise I dont see how you created long names in first place. it should have truncated the name on ADD CONSTRAINT, so you would not have long name in information_schema.key_column_usage — Vao Tsun
– Vao Tsun, Commented Apr 24, 2017 at 10:16
Same error trying to run create function that return RETURNS SETOF "TABLE(seq integer, path_id integer, path_seq integer, node bigint, edge bigint, cost double precision, agg_cost double precision)" : ` identifier "TABLE(seq..." will be truncated to... ` and then ERROR: type "TABLE(seq integer, path_id integer, path_seq integer, node bigi" does not exist — Magno C
– Magno C, Commented Jun 10, 2017 at 2:11

Pat Marx · Accepted Answer · 2018-05-22 17:07:08Z

4

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

edited May 22, 2018 at 17:07

answered May 22, 2018 at 17:00

Pat Marx

3391 gold badge2 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to avoid identifier truncation in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related