CODEWITHSUNDEEP
postgresql
kushj
kushj

Reputation: 137

Postgres | Ensure at least one row is available

I have a table with following columns:

type varchar
value varchar
column_1 <> 
column_2 <>
....

I want to ensure whenever I update or delete in the table, following condition should always hold:

For each type, there should be at least one row with value = default

So for example:

id | Type | Value
-----------------
1  | T1   | v1
2  | T2   | default
3  | T2   | default
4  | T1   | default

In this case, I cannot delete id 4, since then table will not have any row with default value for T1 type.

Any help would be appreciated. Thanks!

Upvotes: 0

Views: 452

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

You could possibly implement this in the deletion/update logic itself. For example, to do a safe delete according to your rules you could try:

DELETE
FROM yourTable t1
WHERE
    id = 4 AND EXISTS (SELECT 1 FROM yourTable t2
                       WHERE t2.Type = t1.Type AND
                             t2.id <> 4 AND
                             t2.Value = 'default');

You could use a similar exists logic for the updates.

Upvotes: 1

Related Questions