51

Hope this question wasn't asked before. Does anyone know the character limit for domain names? For example if I write this:

CREATE DOMAIN d_complement_activite_etablissement AS character varying

it will create a domain with the name:

d_complement_activite_etabliss

(Yeah, I know how to count, but I want some more info on the subject).

Is there a command that can change this maximum length? Is this length the same for other names (columns, tables etc)?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Florin Vistig
  • 1,639
  • 2
  • 22
  • 31

1 Answers1

75

You ask:

Is there a command that can change this maximum length? Is this length the same for other names (columns, tables etc)?

The manual answers here:

The system uses no more than NAMEDATALEN-1 bytes of an identifier; longer names can be written in commands, but they will be truncated. By default, NAMEDATALEN is 64 so the maximum identifier length is 63 bytes. If this limit is problematic, it can be raised by changing the NAMEDATALEN constant in src/include/pg_config_manual.h.

Bold emphasis mine.

The only way to change it is to hack the source code and recompile PostgreSQL.
Domain names are identifiers like any other. When I execute:

CREATE DOMAIN d_complement_activite_etablissement_or_even_loger_than_that AS text

I get what I ordered (tested on PostgreSQL 8.4 - 11):

d_complement_activite_etablissement_or_even_loger_than_that

There must be some other piece of software between you and your database cropping the name.

Note that characters in UTF encoding occupy 1 to 4 bytes. (But ASCII characters (like used in the example) are encoded with a single byte.)

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • hmm... that's strange. It now works for me too. I use Postgre 9.1 and ran the command from pgAdmin. Thanks – Florin Vistig Nov 22 '11 at 08:16
  • 1
    @FlorinVistig: The SQL Editor of pgAdmin has a nice feature: When you select part of the code before executing, only the **selected part** gets executed, ignoring everything else. You are aware of it, right? Sounds like you tripped over that. – Erwin Brandstetter Nov 22 '11 at 10:21
  • I didn't knew about that feature. But I've worked more on SQL Server where that feature exists, and I know to stay away from it. Don't know what was the reason... – Florin Vistig Nov 23 '11 at 14:10
  • 4
    Please note that manual says 63 BYTES, not characters. This means that, for non-ASCII characters, identifiers are truncated after about 31 characters – Kirill Gamazkov Jul 12 '17 at 07:19