CODEWITHSUNDEEP
postgresql
Jakub
Jakub

Reputation: 2709

PostgreSQL how to build a query which updates data to an existing table but to avoid duplicate value in a specific column

I'm trying to build a very specific PSQL query that has to update a table data adding to a column called sign_order a number that identifies an order of an entity called recipient to sign a document.

To be clear but out of scope is that there is a document that has recipients and the recipients they have to sign that order on a specific order.

I tried initially the following query but the issue is that works if we have no data on the table but soon as it runs on a table filled it generates duplicates for the same type


UPDATE
  recipients
SET
  sign_order = CASE
    "role"
    WHEN 'CONSENTEE' THEN 1
    WHEN 'GUARDIAN' THEN 2
    WHEN 'ASSENTEE' THEN 3
    WHEN 'COUNTERSIGNEE' THEN 4
  END
WHERE
  "role" IN ('CONSENTEE', 'GUARDIAN', 'ASSENTEE', 'COUNTERSIGNEE');

ALTER TABLE recipients ALTER COLUMN sign_order SET NOT NULL;

So what happening here is that is adding the signed order when it finds the case but creates a duplicate for example if that finds already a Guardian it ads as 2 but for the same document we can have multiple Guardian and we have an issue as that is added as Guardian 2 then Guardian 2 but should be Guardian 2 Guardian 3 and so on.

The same affects the rest.

A view of the issue where the recipients under the same document are assigned with the same signing order:

select consentee_id, "role" , count(*)
from recipients
group by "role", consentee_id
order by "count" desc;

Result

enter image description here

The base case order is as in the query;

1 CONSENTEE
2 GUARDIAN
3 ASSENTEE
4 COUNTERSIGNEE

This order needs to be maintained and as an example of the right output running the query should be:

recipient sign_order
CONSENTEE 1
GUARDIAN 2
GUARDIAN 3
GUARDIAN 4
ASSENTEE 5
COUNTERSIGNEE 6
COUNTERSIGNEE 7
COUNTERSIGNEE 8

I need to add 2 constraints as unique for these consentee_id and sign_order and then make the query more complex to solve the problem described.

The ALTER part I believe with the constraints will look as it is

ALTER TABLE recipients ALTER COLUMN sign_order SET NOT NULL 
AND
ADD CONSTRAINT unique_sign_order UNIQUE (consentee_id, sign_order);

However, need some help to achieve the goal of this solving problem issue.

Update to give a better view of the table

enter image description here

Table properties

enter image description here

DDL

CREATE TABLE public.recipients (
    id text NOT NULL,
    name text NOT NULL,
    email text NULL,
    phone text NULL,
    locale text NULL,
    consentee_id text NULL,
    is_primary bool NULL DEFAULT false,
    sign_order int4 NOT NULL,
    "role" text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamptz NULL,
    CONSTRAINT recipients_id_unique UNIQUE (id),
    CONSTRAINT recipients_pkey PRIMARY KEY (id),
    CONSTRAINT recipients_consentee_id_foreign FOREIGN KEY (consentee_id) REFERENCES public.consentees(id) ON DELETE CASCADE
);

Upvotes: 0

Views: 60

Answers (1)

Belayer
Belayer

Reputation: 14886

Assuming you have some as yet unmentioned unique column (PK perhaps) you can assign the sign_order value in a CTE, then the main portion of the query updates the column for the PK. I've also assumed your table also contains more that 1 document to be signed (if not it a pretty limited table) with its own signing order. In the following these column are id and doc_id respectively. If those assumptions are invalid, well do not know if this will work.

The big change from what you had was to move the CASE expression into an order by clause of a row_number window function. Then assign sort_order from the row_number value and that is a CTE. (see demo)

with assign_sign_order ( rid, sorder) as  
     ( select id, row_number() over (partition by doc_id 
                                         order by 
                                               case "role"
                                                    when 'CONSENTEE' then 1
                                                    when 'GUARDIAN'  then 2
                                                    when 'ASSENTEE'  then 3
                                                    when 'COUNTERSIGNEE' then 4
                                                end
                                      ) so
                                      , "role", doc_id
         from recipients order by id 
     ) 
update recipients r
   set sign_order = s.sorder
  from assign_sign_order s 
 where r.id = s.rid ;

An alternative would be to create a ENUM for the "role", this simplifies the above query, and would reduce maintenance to just updating the ENUM (no change to the query). Providing you do not want to remove one. It is also in the demo.

Upvotes: 1

Related Questions