How to create an array to store primary keys?

Question

I have 3 tables (Users, Links and LinkLists) and I want to store user ids and their link ids in an array. However, when I try the code below I get following error:

incompatible types: integer[] and integer

Is there any way that I can store user_id and an array of link ids in a table?

CREATE TABLE Users (
    id serial primary key not null,
    email varchar(64) unique not null,
);

CREATE TABLE Links (
    id serial primary key not null,
    name varchar(64) not null
);

CREATE TABLE LinkLists(
    user_id integer unique not null REFERENCES Users(id),
    links integer [] REFERENCES Links(id) -- problem here --
);

Example:

Users Table*

1 [email protected]

Links Table

1 google.com
2 twitter.com

LinkLists Table

1 [1,2]

When you reference a foreign key the types in both tables must be identical — James
– James, Commented Jun 8, 2019 at 21:57
@JaimeDrq I'm totally aware of that. However, I also know that it is possible to create an array elements linking to primary keys. How can I do that? — Mehmet Ali
– Mehmet Ali, Commented Jun 8, 2019 at 21:59
You can't have foreign keys together with array. Storing the IDs in an array is not a good database design to begin with (e.g. you can't easily prevent duplicates in the array). Using a properly designed many-to-many table is the much better choice here. — user330315
– user330315, Commented Jun 9, 2019 at 0:43
Possible duplicate of PostgreSQL array of elements that each are a foreign key — philipxy
– philipxy, Commented Jun 9, 2019 at 3:37

UgurYilmaz · Accepted Answer · 2019-06-09 00:40:25Z

2

Probably you do not need array data type on LinkList table.

You need only two foreign keys.

CREATE TABLE LinkLists(
    user_id integer unique not null REFERENCES Users(id),
    links integer not null REFERENCES Links(id) 
);

According to me, you do not need 3nd table: LinkLists

Define a user_id FK on links table and refer to users.

Or best approach, use a treeview table, including REFERENCES ownself

edited Jun 9, 2019 at 0:40

user330315

answered Jun 8, 2019 at 22:05

UgurYilmaz

1297 bronze badges

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

2 Comments

Mehmet Ali Over a year ago

Thank you for your suggestion but it will be needed to have 3nd table. So your approach makes sense but can't we create an array to hold primary keys?

wildplasser Over a year ago

You should remove the unique. And add a primary key on both columns.

UgurYilmaz · Accepted Answer · 2019-06-08 22:15:43Z

-1

Actually you can do! Just add a new array type field the users table. And define an after insert trigger function for users table.

And update array field by CTE.

I am using that approach succesfully.

answered Jun 8, 2019 at 22:15

UgurYilmaz

1297 bronze badges

1 Comment

Bobdabear Over a year ago

do you have an example or can you elaborate on/explain this a bit more? I find this interesting if you have it working but there isn't enough here for me, or anyone really, to confidently determine if what I try and what you've suggested are the same, which is important for debugging

Collectives™ on Stack Overflow

How to create an array to store primary keys?

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related