Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZbWkv-0004Zq-8g for pgsql-hackers@arkaria.postgresql.org; Mon, 14 Sep 2015 16:37:25 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZbWku-0007yd-Qy for pgsql-hackers@arkaria.postgresql.org; Mon, 14 Sep 2015 16:37:24 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZbWkt-0007uA-Km for pgsql-hackers@postgresql.org; Mon, 14 Sep 2015 16:37:23 +0000 Received: from mail-bl2on0077.outbound.protection.outlook.com ([65.55.169.77] helo=na01-bl2-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZbWkl-0003Cl-K4 for pgsql-hackers@postgresql.org; Mon, 14 Sep 2015 16:37:22 +0000 Received: from BLUPR11MB0676.namprd11.prod.outlook.com (10.163.216.146) by BLUPR11MB0065.namprd11.prod.outlook.com (10.160.37.155) with Microsoft SMTP Server (TLS) id 15.1.268.17; Mon, 14 Sep 2015 16:37:11 +0000 Authentication-Results: spf=none (sender IP is ) smtp.mailfrom=Jim.Nasby@bluetreble.com; Received: from decina.attlocal.net (2602:306:ce8b:3460:a08a:f9dc:4497:dcf5) by BLUPR11MB0676.namprd11.prod.outlook.com (10.163.216.146) with Microsoft SMTP Server (TLS) id 15.1.268.17; Mon, 14 Sep 2015 16:37:08 +0000 Subject: Re: [PROPOSAL] Covering + unique indexes. To: Anastasia Lubennikova , pgsql-hackers References: <55F2C1BC.3010109@postgrespro.ru> <55F2CCD0.7040608@postgrespro.ru> From: Jim Nasby Message-ID: <55F6F7AD.4080307@BlueTreble.com> Date: Mon, 14 Sep 2015 11:37:01 -0500 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:38.0) Gecko/20100101 Thunderbird/38.1.0 MIME-Version: 1.0 In-Reply-To: <55F2CCD0.7040608@postgrespro.ru> Content-Type: text/plain; charset="utf-8"; format=flowed Content-Transfer-Encoding: 7bit X-Originating-IP: [2602:306:ce8b:3460:a08a:f9dc:4497:dcf5] X-ClientProxiedBy: CO2PR18CA0033.namprd18.prod.outlook.com (25.161.80.43) To BLUPR11MB0676.namprd11.prod.outlook.com (25.163.216.146) X-Microsoft-Exchange-Diagnostics: 1; BLUPR11MB0676; 2:MjuFlAeCU7q+++rv+5WdEch3GYtr4z9TMz8NyBdwifdYsNC8LR85i+OdVBxJxzFbG77lG9VUcXpsHmRaA/HQiqZLzBLa5uN6Vh4odmHLMqVjiKadErUsznqLKQK6SWmyANMp10Nl5gj6FRZF1hMLTuvQaoiClrE2dC1i/A57xF0=; 3:umhkwzVO2hqMduhkEihspyJV7R9/x99tLI1Yin4CM3vSQZIx3bPP7aWc1mdvPEVpFnK6PMhzb7STXMm9iVan7B1eb/gIOX5BcHf+gRJ7Uj795t18ud726EgJYf/Gu5YEtVl4UKBBmAhNZw4tg5RUWA==; 25:XwWeZKjcxBxlDYogywNO2wVbKIAApnoc56kyt19ehAFGu1JYAxKi6NxKbWsc8DjMbf1Cy2SRlF8YFmTq97g+mhnSquOr3YZfRruHPPqiiruVeL+cS5nGUMftl3MPhiGQrsodMimY3XFFqCuFqLBljBLMguTePN5Ey93931KWuHbbMyvE/vcvsSkIPuCMXhdzzkW+P+e+d7/05t439c3Y6z95Du536ZyBdmcPXes7sDp8UDzdJ9VCoGVWhuZ25gJzKKVVQSW3xlK7nPIk2SOEGg==; 4:dZsiBEXWU1E4ybkMIopKpzmM+JrrxNqleBiVDVQcwH0uuGWmnx+OLD2w2r0BVErHQhYaFtbuNQsPiF80t4YZfZTuAlztpQ3pYmm1HAxB+YU+BxQg/aiPlItLurX/I/8Y0Unx7/Koq1jrt0tqaLo7beoQPp+nYfPHUudMPsfjqkpMKKEzOTvUuQA1ylkyhcFujP2leXuCKC52f0WWQLJYQXAGt9LD3EfLMeDugVTGFwnZnE0L2pLhOSYaxdaOBe1t+KaJOoYu6fYLOgkWSDuzu2R1I51ciQEQNmFHp2gq5+cbW4ZJe4yaq2LCLG5Hzula X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:BLUPR11MB0676; X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0; PCL:0; RULEID:(601004)(8121501046)(5005006)(3002001); SRVR:BLUPR11MB0676; BCL:0; PCL:0; RULEID:; SRVR:BLUPR11MB0676; X-Forefront-PRVS: 0699FCD394 X-Forefront-Antispam-Report: SFV:NSPM; SFS:(10009020)(979002)(6009001)(189002)(377454003)(479174004)(199003)(24454002)(36756003)(77156002)(68736005)(83506001)(50466002)(65956001)(62966003)(77096005)(15975445007)(92566002)(106356001)(105586002)(64706001)(4001350100001)(4001540100001)(5004730100002)(59896002)(5007970100001)(65806001)(5001770100001)(40100003)(47776003)(81156007)(5001830100001)(23676002)(97736004)(122386002)(64126003)(5001860100001)(189998001)(5001960100002)(76176999)(107886002)(19580395003)(53416004)(101416001)(86362001)(65816999)(87266999)(50986999)(54356999)(87976001)(46102003)(2950100001)(33656002)(42186005)(69596002)(80316001)(200143003)(3826002)(85282002)(336755003)(18886065003)(969003)(989001)(999001)(1009001)(1019001); DIR:OUT; SFP:1101; SCL:1; SRVR:BLUPR11MB0676; H:decina.attlocal.net; FPR:; SPF:None; PTR:InfoNoRecords; MX:1; A:1; LANG:en; Received-SPF: None (protection.outlook.com: bluetreble.com does not designate permitted sender hosts) X-Microsoft-Exchange-Diagnostics: =?utf-8?B?MTtCTFVQUjExTUIwNjc2OzIzOlArQjIrc3FHMXU3Vkkzekl1c1hZOTlNb3hQ?= =?utf-8?B?MU4weEN6YUlzS1hFRkxQUXRvTFR5UmpPYzdCdFJkNjVwTU9JWUtTTm10b3cz?= =?utf-8?B?OXFzcGFYM0U4NVY4cTNPRnlIOUJwbDNrQVh2R1lybndPZHZkTFhjdHNUWUJL?= =?utf-8?B?VjRFVlYwdkxqVS9LakxIZ2NaZ0YxajJWeHpxcnkvZER2UlFrZFM4Vzg1aVBQ?= =?utf-8?B?elFENS9EOWpTbTNPdVI4VTRFZTVqb2RrMW5PTlV6R1k0MEhHdDY5OU4vck51?= =?utf-8?B?dE9HbExoQnZBa3pDNVVMVDBObFVaSDVQWmlLZGhhUUhYS3VpbTVEbUsvS2p0?= =?utf-8?B?NHpybWJiSjRzYW9ueFlPZVArYjBVdzhyS0dnVG84b3dHbGg5eCtGd0ZpcEpq?= =?utf-8?B?Q3JoMUJNaXFQNXpvZUdlcndjRUxyaEI3S3d2WHdGckNrTmZlWW9Ia282a2gz?= =?utf-8?B?NkVERUdGQXo0b0RsaGxTVVZNdm1nclVUZ2pUTS9HU1JyRWZsSkU4ZHpNSWhG?= =?utf-8?B?K1dxR2dwVndDK0JYc214U3BaZGJlNysrL0c2SWt2WFJkbFVNRFRZc3RxVTdv?= =?utf-8?B?TTFCb3d3djFEMHc3TEI1Q0E4R1hvNjJZWk4zREhlc1N2dVM3WXVCZm5HNjg4?= =?utf-8?B?UGlBK1FvT09YcGhvdkx0ZGhqN3BOUmgrN0NLcjVKQjNlcGkvbjNqczdlT3pZ?= =?utf-8?B?R2U5enFMUG5lVG9sU2JPOXhrTGczVW1tVUJ5ZmpVWU9zSytIWEs5Yi9Md25P?= =?utf-8?B?Si9RemRaN3JhcjlqOWxXZWRoalcwNTgrcEF6cTE2ejgrRzZ2MnZ0NVQ4bEY5?= =?utf-8?B?aXIrZklxYUk1VmM3RDgvT1FUV2JpV3FZVmRQWFNoRnFsTDl2N29aS0hDZFQ5?= =?utf-8?B?UjBWZHNmb2t6WXRIanJWRWJWdEdQYjIwdmpkamwrOHlQMC9Va21JT0RrQmhk?= =?utf-8?B?ZVYxZjhUSm5iOGtwUTAwcGdnUXlkSWRSampNcFYrU0paU1hydEsvVHl4OFMy?= =?utf-8?B?OWZVck9tbk53K1kwTk95SS8wK3R6U1lQVlB5NDY0K2dMQmQ0QUVsSlR1dXBO?= =?utf-8?B?ZytRQlRyS1A0TEFKT1BFcnBwTHhDcGtmY2hzbFJXT0VkVDFIQTNiWUEzV3g5?= =?utf-8?B?UC8wM1RVczRSdER4Yit0Wm1Pb3JjTk9ERXYzTXhzZ1poY1orUVZaM2dUS1FX?= =?utf-8?B?bVZSSDM3VG1lNHd3c0NHNFlPdC9JOXY2d05vcmJlckJ1NEZiY0NsUmk5WW1z?= =?utf-8?B?SCtnQUZldUhxSHNhYXJ1M2Zmd1FycHp6MFIrTjZIOEtQbEp3WFFjbldObXJz?= =?utf-8?B?anB6aWl6YUZGZEN0K3JjdjdHTHBYUmZza0c1V3FnWGhvdFN2NXQ2TENvR0l0?= =?utf-8?B?Nys3M09sblFkdFNEY3hVSXdhNlN6c0VBZitTcHVISldhZXZ2M1JVMTJlT2RW?= =?utf-8?B?bUxqTlRMZzUrZ3NzWGVXNVRYTjRnY0t2d203NGxncVh3U2JHbnFNS2VScnd3?= =?utf-8?B?RVdqd0lFeDRpMHd0TkxGUjl2bXhDbExQOVpzVks0SUxXNVBhd2JSQUlXVGlv?= =?utf-8?B?bDZMZld3by9uejZWWFE1Q3hyQVI4ZFdFcDB3bXhiK3dxTHZGdUVyK2dSQ3RX?= =?utf-8?B?SnVUYWpnY3l0V0JNc0ZzSmlSb0NiTzRnK0V0SE1MZjBjMkw2dGx6bjR3OXhX?= =?utf-8?B?NS9wWXpvNXlqOU9qYUhUV3NKS0t2KzBmUjlRSncxUWE3c2xueDVTdE5Ba2hM?= =?utf-8?B?WHpydmdSUVFkcE9BZWx2ZUVKQkEvaVJvNjlIckJDZGJmTTc1U252VDh2a2RW?= =?utf-8?B?dG5rdUU0K0FYdHVxOFAzU3Z5aGRLMlhOSUlUTk1tby9TSUlOSmNZV0FjaitX?= =?utf-8?B?anZ1V1BBRS9vWmF4QUNsZVFxcVdFQkxYaFY0Tis2azcxTm5PUnVpSmptMzh4?= =?utf-8?B?ak1Ha1pDNWtIcWQ2QkJJU1ZISE03M2VOQ2ExNEttY0pOUG8wMnVneS9tWW9j?= =?utf-8?B?NnlESFVxUnpuUWk5bUdZRUowQ1IyU1pwZ1JKTXdWOW54bkxJc211MWtGcUJT?= =?utf-8?B?SjBxUDgyS1gvcXVWZUYyei90NzhkdWJJZko3OXhONkoxMEtnRVRvZkhqaXF2?= =?utf-8?B?dTZlaUdnUU5XZDRPM2J1em1HdXBKdkt0c0tiOGIxeDFYZWphODJ1SEVPSWZH?= =?utf-8?Q?n/GKouluCs47JPur5Fsb6pJXeavRT5qG2hTOI0LGfo=3D?= X-Microsoft-Exchange-Diagnostics: 1; BLUPR11MB0676; 5:9E8cjbZgzX/P/fGmC9Z9EUByN8+He7aVu7NMrF8oB5NoHsUhnPuCBFgdos06/CdWbKf3uKYLGLLGpRSCAGeU861FBziR+3mjz1u+SGFbwsCQWkhZhePR2eVt3aaq6VfqP0lmXBur9W5vmSu/9Tg2LA==; 24:1sd11RYtZRwExbom8Pf4tyOVOEOYd71vLwSjrm3Dm6BEFhr1s+ij4BnMCVSY0qFmK3dTOVndrAmtCGuKeahcMDJVCs1xH4kyi+9kgQ2tD/g=; 20:TiyX/v/EeJXhJaEbAtSXxv2leCmY10Mziw5JSNTu4UdfptNTSan5IOfOIJZs4XYV63EagmWaBKfvx0zLocJk2w== SpamDiagnosticOutput: 1:23 SpamDiagnosticMetadata: NSPM X-MS-Exchange-CrossTenant-OriginalArrivalTime: 14 Sep 2015 16:37:08.7368 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: BLUPR11MB0676 X-Microsoft-Exchange-Diagnostics: 1; BLUPR11MB0065; 2:XzL0IsfFHEd2XAQLyz9Jeb3M92bcaYQb64C9VpacunWa1EEKbOF12UqoGZxXmt+auxTBeReTMmT7jsFUPu+y74Ldjt+Y51gaWgVAz6KvDOslctJ/foyiR3UVAN2x4IcxKmSMifSUp0boD+rX/vRtZJjxm/n/L5BYDwxHNSm5Rz0=; 23:tJV2nOl8JDDg/HkU1bkEFDVrD2xO5oRjO7gD+BgEIQXGqzoT+f25nsZ0cXBhX0wJddcBDnFf9b7+cbOFGRcJH+UZlWfnUlYHowpMGiJV2csN3MDsKWu/mekXrMOKYtRn9C8Cf+xlLn7t+o4rqVwxcTXTbHa3pC1EzQnRL6du3vMV4NG8/cJO7JTXu79NQvCZ X-OriginatorOrg: bluetreble.com X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org On 9/11/15 7:45 AM, Anastasia Lubennikova wrote: > This idea has obvious restriction. We can set unique only for first > index columns. > There is no clear way to maintain following index. > CREATE INDEX index ON table (c1, c2, c3) UNIQUE ON (c1, c3); > > So I suggest following syntax: > CREATE [UNIQUE {ON FIRST {COLUMN | n_unique_column COLUMNS}} INDEX ON > table_name (column_name1, column_name2 ...); I would use the first (simple) syntax and just throw an error if the user tries to skip a column on the UNIQUE clause. Have you by chance looked to see what other databases have done for syntax? I'm guessing this isn't covered by ANSI but maybe there's already an industry consensus. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers