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 1oQVCH-00054L-Nq for pgsql-novice@arkaria.postgresql.org; Tue, 23 Aug 2022 14:48:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oQVCG-0008B0-MP for pgsql-novice@arkaria.postgresql.org; Tue, 23 Aug 2022 14:48:04 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oQVCG-0008Ap-CL for pgsql-novice@lists.postgresql.org; Tue, 23 Aug 2022 14:48:04 +0000 Received: from mail-lf1-x12c.google.com ([2a00:1450:4864:20::12c]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oQVCD-0006Zw-Rv for pgsql-novice@lists.postgresql.org; Tue, 23 Aug 2022 14:48:03 +0000 Received: by mail-lf1-x12c.google.com with SMTP id s1so17205746lfp.6 for ; Tue, 23 Aug 2022 07:48:01 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc; bh=vothdmIkdVAuCXP3KRhrqF5vM4iF+0zPVDT/v7Ct1eE=; b=p0UTG2hNNTVV1oTyxAfH4VRtgCJdnK7BDoq6aSQyof9fDCYxFABuDshQPRjWcC8HTf X1Q8/W4kyWrl1GktBpHcSW+G7fVjX/kmwr3lI+7/F461WcoSq04iI+NmJOwkYdtr+wU8 OZE10n8slrMRc2gbnlCKmTJXsgAVxRi4YFjTlDRhtvv8da6y5IYfUxdm+37mD8pXmLJI efnYNTTufIyUpVBd69p/ynQFj4pXOm4t4LNzPvp3gJg7cN2RqH8zDaVBu+HqdilcOsx9 NTdonpUK5x3ArdtdYtZLQ8BN/xZE5Ko6tWzi8DijAk2uH2QaggW0cMKeXf+7eVxq2map gq2A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc; bh=vothdmIkdVAuCXP3KRhrqF5vM4iF+0zPVDT/v7Ct1eE=; b=zmKg4w4rDdClNCng3Wo4kOa9VBesV8cPlA1iG1IvqnreWfMUK9e0HeSUXKnPrarGKO l5QApsuZREsUTifN1SRTV/8i6dR9dR7I4IHlkJASpUJZMsLFjdZU8AGoZpvgDlesVYpc XqlsAIj+YAK4zQwokLD/I1kmQ7Bd8J+vj6orm6cRNh3ScN/SsfGoKiKn9xuGTgVWQJA7 dgzjksHl7QnCRu/FoJ9rRvBOYVG1APXQKKZcsAL0Osc/ziJ1DN/8az4edQ4SoICvpnir TeOnutq9q9sfcohvHStKCKiXoH2R6EEAeFWfva3YGGjW9GEYGbrbZ6ws2YGiMDt9p3if IzHg== X-Gm-Message-State: ACgBeo2ESPdjXiVkzeYba7nDOI+oKR8/i5a5UDA3LVLenOYFd/tH9J35 kOGJOz7LwpnR6Kr15YJQ85v4aUx/g/AJEZPlzfY= X-Google-Smtp-Source: AA6agR5qFDmMdP3UaujJOU2SHImLp3J++NJqf7USETelN0Rbu50YroiBnJBiNyACLPfxOGDGSJGuF1IWY7ri0ZMX8fU= X-Received: by 2002:a19:e00d:0:b0:492:e5a5:588b with SMTP id x13-20020a19e00d000000b00492e5a5588bmr2926737lfg.243.1661266080148; Tue, 23 Aug 2022 07:48:00 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: chidamparam muthusamy Date: Tue, 23 Aug 2022 20:17:46 +0530 Message-ID: Subject: Re: How to use a cross column exclude constraint To: awolchute@tutanota.com Cc: Pgsql Novice Content-Type: multipart/alternative; boundary="00000000000095b36005e6e9a6aa" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000095b36005e6e9a6aa Content-Type: text/plain; charset="UTF-8" Refer https://www.postgresql.org/docs/current/ddl-constraints.html CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price > 0), discounted_price numeric CHECK (discounted_price > 0), CHECK (price > discounted_price) ); In the above example, column 'discounted_price' value is checked that it should be less than the column value, 'price'. Is it possible to add similar check condition for the columns, 'record_a_id' and 'record_b_id' that is CHECK(record_a_id != record_b_id) Regards, Chidamparam On Mon, Aug 22, 2022 at 1:15 AM wrote: > > 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! > > > --00000000000095b36005e6e9a6aa Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Refer=C2=A0https://www.postgresql.org/docs/current/ddl-constraint= s.html

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price=
 > discounted_price)
);
In the above example, column 'discounted_price'=C2=A0 valu= e is checked that it should be less than the column value, 'price'.=
Is it possible to add similar check condition for the columns, 'record= _a_id' and 'record_b_id' that is
CHECK(record_a_id !=3D record_= b_id)
Regards,
Chidamparam
<= /div>


On Mon, Aug 22, 2022 at 1:15 AM <awolchute@tutanota.com> wrote:
=20 =20 =20

Hi All,

Schema:
CREATE T=
ABLE record (   =20
    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 uuid=C2=A0not null references record (id),
    ...
);

I am trying to create a= n 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".=C2=A0

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


Thank you in advance!


--00000000000095b36005e6e9a6aa--