Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tBE5n-002aZL-4R for pgsql-docs@arkaria.postgresql.org; Wed, 13 Nov 2024 14:11:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tBE5k-00EkyG-3D for pgsql-docs@arkaria.postgresql.org; Wed, 13 Nov 2024 14:11:32 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tBC8l-00DxdB-Qo for pgsql-docs@lists.postgresql.org; Wed, 13 Nov 2024 12:06:32 +0000 Received: from mail-lf1-x12d.google.com ([2a00:1450:4864:20::12d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tBC8j-001f6q-DL for pgsql-docs@lists.postgresql.org; Wed, 13 Nov 2024 12:06:31 +0000 Received: by mail-lf1-x12d.google.com with SMTP id 2adb3069b0e04-53da2140769so503309e87.3 for ; Wed, 13 Nov 2024 04:06:29 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1731499586; x=1732104386; darn=lists.postgresql.org; h=to:date:message-id:subject:mime-version:from:from:to:cc:subject :date:message-id:reply-to; bh=ZRypCGBlv6T9XOHE5aIagH/aGmRYq8uDtImsaio3Dqo=; b=isEV5PVEEITi+Crfs1/LEgYaOCWyfvspZQ8uuHgQWlwR58SG83mvBtlO+SovbPvFSp M9U/LBdvQOtC2s5COuH8R15jddgLPugV0cYoq3k8ecKglNKdwSzewHNS40Ay90B2zoJ/ Euw5QL+eOSEOjSzlwaXmfHXh/CSDRPr2HKdy5niDEtZTMQEd5OtR4Dy/99QUV6CFHKbd 8pMyn2jOK9qNA5sh/DaXJPh45EceZtUe3ekrdbxHqp2cJbzLUEmVTIZIjCyCD+vuZzAg yzK5JIsUNw9hF24AVvtq2CTih3irNFbmX3IwvxQIycUXmvHsxZ29ZAF9iSj521EB2jIN w22Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1731499586; x=1732104386; h=to:date:message-id:subject:mime-version:from:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=ZRypCGBlv6T9XOHE5aIagH/aGmRYq8uDtImsaio3Dqo=; b=v5aJR8NVKgxob3/GJLjPZ7sl27/Z6+YEdykHU+BCtE2nIhF1ok6+pJLGgvzIFGzxIz FQJB/V1mH1uvB0A6G9p+m81++6CtrtbIrb5QtarvcUw6NXN6l8z8gunwuNHHJkZvNcR8 e6ZbghTtC5xALaBCtya2tQ4nRccuM5SrxJTRIkIHVoNwjq0Qj3GX16j1kYi58zDFEX4w 6LwMV8gHqbYqZATCjOKdSpRSSy6P8hl2uoRRnRYnpk62OZD+R0SWR56IQhqBSWRtQ8Nb R4kwsUfQc0XbMMh6ts+RWb1UVMJNtw5gc+uhvWQscUvnJG5M2ilD+RFNAMo2MohLtehA 6gUw== X-Gm-Message-State: AOJu0Yw6fn8AIfYLj/BPQGG+gbY3qLa3SswUo5VTui6czwhTnSkPwdEz Mp0mAtNeHFx3VHamRT2G4w0OmS4ZhOoqzR4063SDiNnaa6z4yQu3R3a8XQ== X-Google-Smtp-Source: AGHT+IEsqisX9IdQEAX2vXJzy4ZZd5fEt5LTmELGt/rVCu5sJhcU756ThwmKyQeTlglBgzuMm2qkvQ== X-Received: by 2002:a05:6512:3f10:b0:53d:a292:92c with SMTP id 2adb3069b0e04-53da2920a4bmr706834e87.43.1731499585903; Wed, 13 Nov 2024 04:06:25 -0800 (PST) Received: from smtpclient.apple ([176.226.234.225]) by smtp.gmail.com with ESMTPSA id 2adb3069b0e04-53d826af0c6sm2169649e87.277.2024.11.13.04.06.23 for (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 13 Nov 2024 04:06:24 -0800 (PST) From: "=?utf-8?B?ItCh0LXRgNCz0LXQuSDQnyAoU2VyZ2VpRG9zKSI=?=" X-Google-Original-From: =?utf-8?B?ItCh0LXRgNCz0LXQuSDQnyAoU2VyZ2VpRG9zKSI=?= Content-Type: multipart/alternative; boundary="Apple-Mail=_69505D89-97D2-40D5-8A0F-598EFD6FCED6" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.200.121\)) Subject: Generated names (suffix) for constraints not described in docs Message-Id: <712E40A2-744F-424E-AE45-95CE5BE27D82@gmail.com> Date: Wed, 13 Nov 2024 17:06:12 +0500 To: pgsql-docs@lists.postgresql.org X-Mailer: Apple Mail (2.3826.200.121) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_69505D89-97D2-40D5-8A0F-598EFD6FCED6 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii = = = = Hello! In the documentation for the Constraints section = https://www.postgresql.org/docs/current/ddl-constraints.html there is a = phrase: "So, to specify a named constraint, use the key word CONSTRAINT = followed by an identifier followed by the constraint definition. (If you = can't specify a constraint name in this way, the system choose a name = for you.)" But nowhere in the documentation are the rules by which it generates = names on its own described. For example, the code: CREATE TABLE IF NOT EXISTS = test_table_with_very_long_table_name_over_sixty_four_symbols ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, code VARCHAR ); CREATE TABLE IF NOT EXISTS = test_table_2_with_very_long_table_name_over_sixty_four_symbols ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, not_very_long_id_from BIGINT NOT NULL REFERENCES = test_table_with_very_long_table_name_over_sixty_four_symbols (id), not_very_long_id_to BIGINT NOT NULL REFERENCES = test_table_with_very_long_table_name_over_sixty_four_symbols (id) ); empirically we find out that two constraints are generated: test_table_2_with_very_long_table_name_not_very_long_id_to_fkey test_table_2_with_very_long_table_na_not_very_long_id_from_fkey Table name + column name + suffix _fkey In this case, the table name is truncated so that the total length of = the name is no more than 63 characters. But the rules for forming this string are not described in the = documentation. The documentation also does not include other suffixes that the system = generates: pkey for a Primary Key constraint; key for a Unique constraint; excl for an Exclusion constraint; idx for any other kind of index; fkey for a Foreign key; check for a Check constraint; Standard suffix for sequences is seq for all sequences Best regards Podrezov Sergey = = = --Apple-Mail=_69505D89-97D2-40D5-8A0F-598EFD6FCED6 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii
Hello!
In the documentation = for the Constraints section = https://www.postgresql.org/docs/current/ddl-constraints.html there is a = phrase: "So, to specify a named constraint, use the key word CONSTRAINT = followed by an identifier followed by the constraint definition. (If you = can't specify a constraint name in this way, the system choose a name = for you.)"
But nowhere in the = documentation are the rules by which it generates names on its own = described.

For example, the = code:
CREATE TABLE IF NOT = EXISTS = test_table_with_very_long_table_name_over_sixty_four_symbols
(
id BIGINT GENERATED = ALWAYS AS IDENTITY PRIMARY KEY,
code = VARCHAR
);

CREATE TABLE IF NOT = EXISTS = test_table_2_with_very_long_table_name_over_sixty_four_symbols
(
 id BIGINT = GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
 not_very_long_id_from BIGINT NOT NULL REFERENCES = test_table_with_very_long_table_name_over_sixty_four_symbols = (id),
 not_very_long_id_to BIGINT NOT NULL REFERENCES = test_table_with_very_long_table_name_over_sixty_four_symbols = (id)
);

empirically we find = out that two constraints are generated:

test_table_2_with_very_long_table_name_not_very_long_id_= to_fkey
test_table_2_with_very_long_table_na_not_very_long_id_fr= om_fkey

Table name + column = name + suffix _fkey
In this case, the = table name is truncated so that the total length of the name is no more = than 63 characters.

But the rules for = forming this string are not described in the documentation.

The documentation = also does not include other suffixes that the system = generates:
pkey for a Primary = Key constraint;
key for a Unique = constraint;
excl for an Exclusion = constraint;
idx for any other = kind of index;
fkey for a Foreign = key;
check for a Check = constraint;
Standard suffix for = sequences is
seq for all = sequences

Best = regards
Podrezov = Sergey
= --Apple-Mail=_69505D89-97D2-40D5-8A0F-598EFD6FCED6--