How do I make an array of ids in one column is Postgresql database?

Question

I have the permissions table in postgres table

CREATE TABLE public.permissions
(
permission_id integer NOT NULL,
crud_signature text COLLATE pg_catalog."default" NOT NULL,
object_id uuid NOT NULL,
object_type text COLLATE pg_catalog."default" NOT NULL,
include_subcategories boolean NOT NULL,
CONSTRAINT permissions_pkey PRIMARY KEY (permission_id)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.permissions
OWNER to postgres;

and have ROLES table. Each role has multiple permission.

CREATE TABLE public.roles
(
role_id integer NOT NULL,
role_name text COLLATE pg_catalog."default" NOT NULL,
role_description text COLLATE pg_catalog."default",
permissions integer[],
CONSTRAINT roles_pkey PRIMARY KEY (role_id)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.roles OWNER to postgres;

When I try to make a foreign keys column like permissions of type integer[] Posgres gives me an error that theese fields have incompartables types - integer and integer[]

What should I do?

Please include enough sample input and output data in your question to make it clear what you are trying to do here. — Tim Biegeleisen
– Tim Biegeleisen, Commented Apr 15, 2019 at 5:31
I try to make a permissions field in ROLES table a foreign key. And it should be an array of permissions keys — art
– art, Commented Apr 15, 2019 at 5:33

Tim Biegeleisen · Accepted Answer · 2019-04-15 05:37:08Z

2

I would actually recommend not using arrays here. Instead, use a junction table to represent each role-permission relationship, something like this:

CREATE TABLE roles_permissions (
    role_id integer NOT NULL,
    permission_id integer NOT NULL,
    CONSTRAINT rp_pkey PRIMARY KEY (role_id, permission_id)
);

Then modify your current roles table by removing the array of permissions. Your current approach uses unnormalized data, because you are trying to store a set of permissions in an array for each separate role.

answered Apr 15, 2019 at 5:37

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I make an array of ids in one column is Postgresql database?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related