Update unique values into Postgres Array
I have a situation where I update a Postgres table column which is of type bigint. This array should have unique numbers inside it whenever an update query is fired.
The query is as given below
UPDATE book_shelf
SET book_id = book_id || array[CAST(:bookID AS BIGINT)], updated_at = now()
WHERE user_id = :userID AND shelf_name = :shelfName
When the above query is fired, it simply adds the number into the array which I don't want to happen. It should hold only unique values. Please help me.
spring postgresql hibernate spring-data-jpa
asked Nov 16 '18 at 9:19
madhusdhnnmadhusdhnn
4819
add a comment |
I have a situation where I update a Postgres table column which is of type bigint. This array should have unique numbers inside it whenever an update query is fired.
The query is as given below
UPDATE book_shelf
SET book_id = book_id || array[CAST(:bookID AS BIGINT)], updated_at = now()
WHERE user_id = :userID AND shelf_name = :shelfName
When the above query is fired, it simply adds the number into the array which I don't want to happen. It should hold only unique values. Please help me.
spring postgresql hibernate spring-data-jpa
asked Nov 16 '18 at 9:19
madhusdhnnmadhusdhnn
4819
add a comment |
I have a situation where I update a Postgres table column which is of type bigint. This array should have unique numbers inside it whenever an update query is fired.
The query is as given below
UPDATE book_shelf
SET book_id = book_id || array[CAST(:bookID AS BIGINT)], updated_at = now()
WHERE user_id = :userID AND shelf_name = :shelfName
When the above query is fired, it simply adds the number into the array which I don't want to happen. It should hold only unique values. Please help me.
spring postgresql hibernate spring-data-jpa
asked Nov 16 '18 at 9:19
madhusdhnnmadhusdhnn
4819
I have a situation where I update a Postgres table column which is of type bigint. This array should have unique numbers inside it whenever an update query is fired.
The query is as given below
UPDATE book_shelf
SET book_id = book_id || array[CAST(:bookID AS BIGINT)], updated_at = now()
WHERE user_id = :userID AND shelf_name = :shelfName
When the above query is fired, it simply adds the number into the array which I don't want to happen. It should hold only unique values. Please help me.
spring postgresql hibernate spring-data-jpa
spring postgresql hibernate spring-data-jpa
asked Nov 16 '18 at 9:19
madhusdhnnmadhusdhnn
4819
asked Nov 16 '18 at 9:19
madhusdhnnmadhusdhnn
4819
asked Nov 16 '18 at 9:19
madhusdhnnmadhusdhnn
4819
asked Nov 16 '18 at 9:19
madhusdhnnmadhusdhnn
4819
asked Nov 16 '18 at 9:19
madhusdhnnmadhusdhnn
4819
4819
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You can check if it exists in the array before adding it:
UPDATE book_shelf
SET book_id = CASE WHEN CAST(:bookID AS BIGINT) = ANY(book_id) THEN book_id ELSE ARRAY_APPEND(book_id, CAST(:bookID AS BIGINT)) END, updated_at = now()
WHERE user_id = :userID AND shelf_name = :shelfName
Of course if updated_at should only be set if book_id is actually updated, then put the check in the WHERE clause so it's not updated unnecessarily:
UPDATE book_shelf
SET book_id = ARRAY_APPEND(book_id, CAST(:bookID AS BIGINT)), updated_at = now()
WHERE user_id = :userID
AND shelf_name = :shelfName
AND NOT CAST(:bookID AS BIGINT) = ANY(book_id)
answered Nov 16 '18 at 9:47
404404
3,2451828
Thanks a lot. Works like a charm
– madhusdhnn
Nov 16 '18 at 10:36
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f53334776%2fupdate-unique-values-into-postgres-array%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can check if it exists in the array before adding it:
UPDATE book_shelf
SET book_id = CASE WHEN CAST(:bookID AS BIGINT) = ANY(book_id) THEN book_id ELSE ARRAY_APPEND(book_id, CAST(:bookID AS BIGINT)) END, updated_at = now()
WHERE user_id = :userID AND shelf_name = :shelfName
Of course if updated_at should only be set if book_id is actually updated, then put the check in the WHERE clause so it's not updated unnecessarily:
UPDATE book_shelf
SET book_id = ARRAY_APPEND(book_id, CAST(:bookID AS BIGINT)), updated_at = now()
WHERE user_id = :userID
AND shelf_name = :shelfName
AND NOT CAST(:bookID AS BIGINT) = ANY(book_id)
answered Nov 16 '18 at 9:47
404404
3,2451828
Thanks a lot. Works like a charm
– madhusdhnn
Nov 16 '18 at 10:36
add a comment |
You can check if it exists in the array before adding it:
UPDATE book_shelf
SET book_id = CASE WHEN CAST(:bookID AS BIGINT) = ANY(book_id) THEN book_id ELSE ARRAY_APPEND(book_id, CAST(:bookID AS BIGINT)) END, updated_at = now()
WHERE user_id = :userID AND shelf_name = :shelfName
Of course if updated_at should only be set if book_id is actually updated, then put the check in the WHERE clause so it's not updated unnecessarily:
UPDATE book_shelf
SET book_id = ARRAY_APPEND(book_id, CAST(:bookID AS BIGINT)), updated_at = now()
WHERE user_id = :userID
AND shelf_name = :shelfName
AND NOT CAST(:bookID AS BIGINT) = ANY(book_id)
answered Nov 16 '18 at 9:47
404404
3,2451828
Thanks a lot. Works like a charm
– madhusdhnn
Nov 16 '18 at 10:36
add a comment |
You can check if it exists in the array before adding it:
UPDATE book_shelf
SET book_id = CASE WHEN CAST(:bookID AS BIGINT) = ANY(book_id) THEN book_id ELSE ARRAY_APPEND(book_id, CAST(:bookID AS BIGINT)) END, updated_at = now()
WHERE user_id = :userID AND shelf_name = :shelfName
Of course if updated_at should only be set if book_id is actually updated, then put the check in the WHERE clause so it's not updated unnecessarily:
UPDATE book_shelf
SET book_id = ARRAY_APPEND(book_id, CAST(:bookID AS BIGINT)), updated_at = now()
WHERE user_id = :userID
AND shelf_name = :shelfName
AND NOT CAST(:bookID AS BIGINT) = ANY(book_id)
answered Nov 16 '18 at 9:47
404404
3,2451828
You can check if it exists in the array before adding it:
UPDATE book_shelf
SET book_id = CASE WHEN CAST(:bookID AS BIGINT) = ANY(book_id) THEN book_id ELSE ARRAY_APPEND(book_id, CAST(:bookID AS BIGINT)) END, updated_at = now()
WHERE user_id = :userID AND shelf_name = :shelfName
Of course if updated_at should only be set if book_id is actually updated, then put the check in the WHERE clause so it's not updated unnecessarily:
UPDATE book_shelf
SET book_id = ARRAY_APPEND(book_id, CAST(:bookID AS BIGINT)), updated_at = now()
WHERE user_id = :userID
AND shelf_name = :shelfName
AND NOT CAST(:bookID AS BIGINT) = ANY(book_id)
answered Nov 16 '18 at 9:47
404404
3,2451828
answered Nov 16 '18 at 9:47
404404
3,2451828
answered Nov 16 '18 at 9:47
404404
3,2451828
answered Nov 16 '18 at 9:47
404404
3,2451828
3,2451828
Thanks a lot. Works like a charm
– madhusdhnn
Nov 16 '18 at 10:36
add a comment |
Thanks a lot. Works like a charm
– madhusdhnn
Nov 16 '18 at 10:36
Thanks a lot. Works like a charm
– madhusdhnn
Nov 16 '18 at 10:36
Thanks a lot. Works like a charm
– madhusdhnn
Nov 16 '18 at 10:36
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53334776%2fupdate-unique-values-into-postgres-array%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
