Search code examples
postgresqlkeywordidentifier

Maximum characters in labels (table names, columns etc)

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)?

Solution

  • 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. Note: bytes, not characters.
    Characters in UTF-8 encoding occupy 1 to 4 bytes. But ASCII characters (like used in the example) are encoded with a single byte.

    The only way to change the length restriction 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_longer_than_that AS text

    I get what I ordered for an identifier of 60 ASCII characters. Tested on PostgreSQL 8.4 - 16:

    d_complement_activite_etablissement_or_even_longer_than_that

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