Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oPqsv-0000Xe-UN for pgsql-novice@arkaria.postgresql.org; Sun, 21 Aug 2022 19:45:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oPqsu-0003xt-FD for pgsql-novice@arkaria.postgresql.org; Sun, 21 Aug 2022 19:45:24 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oPqsu-0003xj-57 for pgsql-novice@lists.postgresql.org; Sun, 21 Aug 2022 19:45:24 +0000 Received: from w1.tutanota.de ([81.3.6.162]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oPqsp-00075i-OW for pgsql-novice@lists.postgresql.org; Sun, 21 Aug 2022 19:45:23 +0000 Received: from w3.tutanota.de (unknown [192.168.1.164]) by w1.tutanota.de (Postfix) with ESMTP id A2EA2FA0451 for ; Sun, 21 Aug 2022 19:45:17 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; t=1661111117; s=s1; d=tutanota.com; h=From:From:To:To:Subject:Subject:Content-Description:Content-ID:Content-Type:Content-Type:Content-Transfer-Encoding:Cc:Date:Date:In-Reply-To:MIME-Version:MIME-Version:Message-ID:Message-ID:Reply-To:References:Sender; bh=uxvO9blShDVmksRpv9q32al8A0ZYvncTEDLfHNXeh6w=; b=JWkBewwDBQPQ/vdkcWPOcCdZjkM3EI5SiDcVa27jOv5larnGghbw1k8LdN17v0RQ 3dt7CVAWzgETaKih/4TYVwPKBaDHli04d0Y08e+j2JzQlm4RHD7Gm89iKOXYPUJn00o NmsB15TSRLTQjIGWmeRVw+l9LsbXIOi/JhBUWoXqMIKlMbTWfCpwKr+OKxyfUNhkLjs s1kCvutPOGQrKoEEIbJCD1bAXUTwbq1QwNCDWkLfr4GWZPxs4sH8iIgq1SW/+BdeKoa y2FnduxPS84+Ae0/kvoldOBsxFpW2d60W/cvUZmBrnSDbRPo+P9EeAI5XCmpqlCtWla +KrMrnklVA== Date: Sun, 21 Aug 2022 21:45:17 +0200 (CEST) From: awolchute@tutanota.com To: Pgsql Novice Message-ID: Subject: How to use a cross column exclude constraint MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_877440_1561249341.1661111117651" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_877440_1561249341.1661111117651 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi All, Schema: CREATE TABLE record ( id uuid=C2=A0primary key default ..., ...);= CREATE TABLE record_pointer=C2=A0( id uuid=C2=A0primary key default ...,= record_a_id uuid=C2=A0not null references record (id), record_b_id u= uid=C2=A0not null references record (id), ...); I am trying to create an exclude constraint to make both record_a_id and re= cord_b_id unique table wide, so that each row from "record" can ever be ref= erenced once in "record_pointers".=C2=A0 Eg. if I add a record_pointer row that has "this-is-random-uuid" as record_= a_id, the value "this-is-random-uuid" can never be in record_a_id or record= _b_id in any other row. Thank you in advance! ------=_Part_877440_1561249341.1661111117651 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit

Hi All,

Schema:

CREATE TABLE record (    
    id uuid primary key default ...,
    ...
);

CREATE TABLE record_pointer (
    id uuid primary key default ...,
    record_a_id uuid not null references record (id),
    record_b_id uuid not null references record (id),
    ...
);

I am trying to create an exclude constraint to make both record_a_id and record_b_id unique table wide, so that each row from "record" can ever be referenced once in "record_pointers".

Eg. if I add a record_pointer row that has "this-is-random-uuid" as record_a_id, the value "this-is-random-uuid" can never be in record_a_id or record_b_id in any other row.

Thank you in advance!

------=_Part_877440_1561249341.1661111117651--