Multiple ID's in a field. Postgresql

Question

So, i'm currently working with a database system where a user can register, login and update his/her details whenever. The database includes 5 roles:

1. Public
2. Member
3. Moderator
4. Coordinator
5. Admin

I want to be able to assign multiple roles to my users. For example, the Admin can also be a member. Therefore in the database it should show:

User_id    |    Role_ID
------------------------
user1      | 2, 5

^ is it possible to add multivalued id's in postgresql?

score 3 · Accepted Answer · answered Sep 13 '13 at 03:19

You can use filed of type array to store list of values. However I think that there is much better way to organize what you want.

Make one table: role_names and another roles, like that:

CREATE TABLE role_names
(
  id serial NOT NULL,
  name text NOT NULL,
  CONSTRAINT role_names_pkey PRIMARY KEY (id),
  CONSTRAINT role_names_name_key UNIQUE (name)
);

CREATE TABLE roles
(
  user_id bigint NOT NULL,
  role_id bigint NOT NULL,
  CONSTRAINT roles_pkey PRIMARY KEY (user_id, role_id),
  CONSTRAINT roles_role_id_fkey FOREIGN KEY (role_id)
      REFERENCES role_names (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
);

Now in the table role_names you put all the roles that you want to have. In the table roles, you can assign or delete any number of roles to any user. Also you can search in table roles for specific users or specific roles - much neat and faster than searching into arrays I think.

Feel free to add FK constraint for the user_id field too.

This also works, have both the answers a shot and they both worked. Thank you so much — Anuj Hari, Sep 18 '13 at 23:28

score 1 · Answer 2 · edited May 23 '17 at 12:05

1

Yes, you can use int array to store list of roles.

Here's related question -Junction tables vs foreign key arrays?

edited May 23 '17 at 12:05

Community

1
1

answered Sep 13 '13 at 03:04

Roman Pekar

107,110
28
195
197

what if the integer is a foreign key and it references to the role table where the ID's are set. Because i tried doing role_id int[] references role; and that didn't work – Anuj Hari Sep 13 '13 at 03:07
Key columns "role_id" and "id" are of incompatible types: integer[] and integer. – Anuj Hari Sep 13 '13 at 03:08
As far as I know, you cannot create foreign key for elements of array. You can do this with trigger, or use table user_id, role_id to store multiple roles – Roman Pekar Sep 13 '13 at 03:35

Multiple ID's in a field. Postgresql

2 Answers2