From: | Rui DeSousa <rui(dot)desousa(at)icloud(dot)com> |
---|---|
To: | M Sarwar <sarwarmd02(at)outlook(dot)com> |
Cc: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, "wolters(dot)k(at)web(dot)de" <wolters(dot)k(at)web(dot)de>, "pgsql-admin(at)lists(dot)postgresql(dot)org" <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints |
Date: | 2024-05-23 02:42:13 |
Message-ID: | 1E8736C1-ACF4-4250-9B0B-789A6D927958@icloud.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
> On May 22, 2024, at 9:22 PM, M Sarwar <sarwarmd02(at)outlook(dot)com> wrote:
>
> Hi David,
> I did verify at Ver 13 and this ( UNIQUE NULLS NOT DISTINCT ) syntax is not existing there. That means, I need to work on it without this option.
> My team will not upgrade from 13 due to number of reasons.
In that case you could use a functional unique index with a digest to teat nulls as not distinct instead; although I do prefer NULL to be distinct. To me this indicates that the schema is not fully normalized and/or ported from MS SQL server.
I’ve used this approach when dealing with organic schemas that had simular logic and requirements.
x_idx1 — is to fulfill queries and traditional unique values. (Better to use a constraint instead for correctness/readability)
x_idx2 — handles uniqueness when one of values is null. (Must be an index as constraints do not support functional indexes)
prod=> \d x
Table "rui.x"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
b | integer | | |
c | integer | | |
Indexes:
"x_idx1" UNIQUE, btree (a, b, c)
"x_idx2" UNIQUE, btree (pgcrypto.digest((COALESCE(a::text, '<null>'::text) || COALESCE(b::text, '<null>'::text)) || COALESCE(c::text, '<null>'::text), 'sha256'::text)) WHERE a IS NULL OR b IS NULL OR c IS NULL
prod=> insert into x values (1, 2, null);
INSERT 0 1
Time: 1.741 ms
prod=> insert into x values (1, 2, null);
ERROR: duplicate key value violates unique constraint "x_idx2"
DETAIL: Key (pgcrypto.digest((COALESCE(a::text, '<null>'::text) || COALESCE(b::text, '<null>'::text)) || COALESCE(c::text, '<null>'::text), 'sha256'::text))=(\x7cce718aefddfbd5db7925f15b0ab319d7f06b4aeae096a1542f8d1adeef36be) already exists.
Time: 0.843 ms
prod=> insert into x values (2, 2, 2);
INSERT 0 1
Time: 2.451 ms
prod=> insert into x values (2, 2, 2);
ERROR: duplicate key value violates unique constraint "x_idx1"
DETAIL: Key (a, b, c)=(2, 2, 2) already exists.
Time: 0.698 ms
From | Date | Subject | |
---|---|---|---|
Next Message | Rui DeSousa | 2024-05-23 03:10:09 | Re: Need guidance on partioning |
Previous Message | M Sarwar | 2024-05-23 01:22:28 | Re: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints |