Help Center/ GaussDB/ Developer Guide(Distributed_8.x)/ Development and Design Proposal/ Database Object Naming Conventions
Updated on 2024-08-20 GMT+08:00
View PDF
Share

Database Object Naming Conventions

Database object names must meet the following requirements:

  • The name length of a table of an identifier cannot exceed 63 bytes. If you need to copy a table with a global secondary index (GSI) or import GDS data to the table, the length of the table name cannot exceed 38 bytes.
  • An identifier starts with a letter or underscore (_) and can contain letters, digits, underscores (_), dollar signs ($), and number signs (#).
  • If an identifier is enclosed in backquotes (``) in MySQL-compatible mode or double quotation marks (""), any combination of valid characters can be used, for example, "123gs_column".
  • Identifiers are case-insensitive. They are case-sensitive only when they are enclosed in backquotes (``) in MySQL-compatible mode or double quotation marks ("").
  • The gsql shortcut commands (except \sf) cannot be used to query the object names enclosed in backquotes.
  • Do not use reserved or non-reserved keywords to name database objects.

    To query the GaussDB keywords, run select * from pg_get_keywords() or refer to Keywords.

  • Do not use a string enclosed in double quotation marks ("") to define the database object name, unless you need to specify its capitalization. Case sensitivity of database object names makes problem location difficult.
  • Use the same naming format for database objects.
    • In a system undergoing incremental development or service migration, you are advised to comply with its historical naming conventions.
    • You are advised to use multiple words separated with underscores (_).
    • You are advised to use intelligible names and common acronyms or abbreviations for database objects. Acronyms or abbreviations that are generally understood are recommended. For example, you can use English words or Chinese pinyin indicating actual business terms. The naming format should be consistent within a cluster.
    • A variable name must be descriptive and meaningful. It must have a prefix indicating its type.
  • The name of a table object should indicate its main characteristics, for example, whether it is an ordinary, temporary, or unlogged table.
    • An ordinary table name should indicate the business relevant to a dataset.
    • Temporary tables are named in the format of tmp_Suffix.
    • Unlogged tables are named in the format of ul_Suffix.
    • Foreign tables are named in the format of f_Suffix.
    • Do not create database objects whose names start with redis_.
    • Do not create database objects named pgxc_redistb.
    • Do not create database objects whose names start with mlog_ or matviewmap_.
    • Do not create database objects whose names start with gs_role_.
  • The name of a table object shall not exceed 63 bytes. If the length of a table name exceeds this value, the kernel truncates the table name. As a result, the actual name is inconsistent with the configured value. In addition, characters may be truncated in different character sets and unexpected characters may appear.
  • If you need to copy a table with a global secondary index (GSI) or import GDS data to the table, the length of the table name cannot exceed 38 bytes. If the length exceeds the maximum, the temporary table name created during COPY or GDS import may be truncated. As a result, the data import process is interrupted and unexpected characters are generated.