Removing values from array in postgreSQL
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
Lets say I have a table called users, who looks something like the following:
users table:
|user_id | name |
1 Bob
2 Alice
3 Joe
4 Tom
And another table called books, who got a "users" columns of array of integers relates to users ids who own the books (type: ARRAY::integer)
books:
name num_pages users
mybook1 130 {4,2,1,44}
mybook2 140 {1,2}
mybook3 145 {5,7,8,10}
unfortunately, some of the users has been deleted, and now I have to remove their user id reference from the table.
My question is - Which query should I run in order to remove from the books table the user_ids who are not exists anymore in "users" table?
for example on "books" table, mybook1 user id 44 does not exist anymore, or mybook3 ids 5,7,8,10 does not exist in users table
(I've created a trigger to handle it from now on when removing users
but how can I remove the current "orphan users" who is not reflected in the users table anymore)
note: I have no foreign keys or something like that in the books table
Thanks a lot
postgresql psql
asked 9 mins ago
Chenko47Chenko47
3614
add a comment |
Lets say I have a table called users, who looks something like the following:
users table:
|user_id | name |
1 Bob
2 Alice
3 Joe
4 Tom
And another table called books, who got a "users" columns of array of integers relates to users ids who own the books (type: ARRAY::integer)
books:
name num_pages users
mybook1 130 {4,2,1,44}
mybook2 140 {1,2}
mybook3 145 {5,7,8,10}
unfortunately, some of the users has been deleted, and now I have to remove their user id reference from the table.
My question is - Which query should I run in order to remove from the books table the user_ids who are not exists anymore in "users" table?
for example on "books" table, mybook1 user id 44 does not exist anymore, or mybook3 ids 5,7,8,10 does not exist in users table
(I've created a trigger to handle it from now on when removing users
but how can I remove the current "orphan users" who is not reflected in the users table anymore)
note: I have no foreign keys or something like that in the books table
Thanks a lot
postgresql psql
asked 9 mins ago
Chenko47Chenko47
3614
add a comment |
Lets say I have a table called users, who looks something like the following:
users table:
|user_id | name |
1 Bob
2 Alice
3 Joe
4 Tom
And another table called books, who got a "users" columns of array of integers relates to users ids who own the books (type: ARRAY::integer)
books:
name num_pages users
mybook1 130 {4,2,1,44}
mybook2 140 {1,2}
mybook3 145 {5,7,8,10}
unfortunately, some of the users has been deleted, and now I have to remove their user id reference from the table.
My question is - Which query should I run in order to remove from the books table the user_ids who are not exists anymore in "users" table?
for example on "books" table, mybook1 user id 44 does not exist anymore, or mybook3 ids 5,7,8,10 does not exist in users table
(I've created a trigger to handle it from now on when removing users
but how can I remove the current "orphan users" who is not reflected in the users table anymore)
note: I have no foreign keys or something like that in the books table
Thanks a lot
postgresql psql
asked 9 mins ago
Chenko47Chenko47
3614
Lets say I have a table called users, who looks something like the following:
users table:
|user_id | name |
1 Bob
2 Alice
3 Joe
4 Tom
And another table called books, who got a "users" columns of array of integers relates to users ids who own the books (type: ARRAY::integer)
books:
name num_pages users
mybook1 130 {4,2,1,44}
mybook2 140 {1,2}
mybook3 145 {5,7,8,10}
unfortunately, some of the users has been deleted, and now I have to remove their user id reference from the table.
My question is - Which query should I run in order to remove from the books table the user_ids who are not exists anymore in "users" table?
for example on "books" table, mybook1 user id 44 does not exist anymore, or mybook3 ids 5,7,8,10 does not exist in users table
(I've created a trigger to handle it from now on when removing users
but how can I remove the current "orphan users" who is not reflected in the users table anymore)
note: I have no foreign keys or something like that in the books table
Thanks a lot
postgresql psql
postgresql psql
asked 9 mins ago
Chenko47Chenko47
3614
asked 9 mins ago
Chenko47Chenko47
3614
edited 1 min ago
Chenko47
asked 9 mins ago
Chenko47Chenko47
3614
asked 9 mins ago
Chenko47Chenko47
3614
asked 9 mins ago
Chenko47Chenko47
3614
3614
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f235383%2fremoving-values-from-array-in-postgresql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f235383%2fremoving-values-from-array-in-postgresql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
