2

How can i remove the value from array field? I need to delete the user id from the university table's user_id array field if exists when i delete any users. so can i do this by using delete cascade or any other method? please help me on this

Table:

CREATE TABLE IF NOT EXISTS users
    (_id SERIAL NOT NULL UNIQUE PRIMARY KEY,
    "userName" VARCHAR NOT NULL,
     active BOOLEAN NOT NULL);

CREATE TABLE IF NOT EXISTS university
    (_id SERIAL NOT NULL UNIQUE PRIMARY KEY,
    "universityName" VARCHAR NOT NULL,
    user_id integer[]);

User:

| _id | userName | active |
===========================
| 1   |  Abc     |   true |
| 2   |  vty     |   true |
| 7   |  hyu     |   true |
| 9   |  Agc     |   true |

University:

| _id | universityName    | user_id |
=====================================
| 1   |  HGV              |  {1,9}  |
| 2   |  CPC              |   {2}   |

1) if i delete user 2 then entire row should get deleted from university 2) if i delete user 1 then only value 1 should be removed from the user_id field from university table

  • 1
    May I suggest creating a table that represents the relationship between university and user that gets rid of the need for an array – Lennart Aug 2 '18 at 9:50
1

First of all you need to create gin index on array field:

CREATE INDEX index_university_user_id ON university USING gin (user_id);

Then you need to add trigger to process deleting users:

CREATE FUNCTION trg_d_users() RETURNS TRIGGER AS $f$
BEGIN
    UPDATE university
       SET user_id = array_remove(user_id, OLD.id)
     WHERE user_id = OLD.id;
    RETURN OLD; 
END
$f$ LANGUAGE plpgsql;

CREATE TRIGGER trg_d_users BEFORE DELETE ON users FOR EACH ROW EXECUTE PROCEDURE trg_d_users()

If you want to remove university without users, you can add into function after UPDATE:

DELETE FROM university WHERE user_id = '{}';

And also I would reccomend to rename user_id field to user_ids. It very useful to see that field is array without checking schema.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.