PostgreSQL. I need a hierarchical table to have a constraint so no node could have the same name on the same...
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have a hierarchical table with duplicate names on the same level. Example -
user (int id, string name, int parent_id)
1, Sam, null
2, Mike, 1
3, Mike, 1
4, Mike, 1
I need to make them like this
1, Sam, null
2, Mike#1, 1
3, Mike#2, 1
4, Mike#3, 1
And somehow add constraint. How can I do that?
sql postgresql
asked Nov 16 '18 at 18:51
Dennis GlotDennis Glot
54210
add a comment |
I have a hierarchical table with duplicate names on the same level. Example -
user (int id, string name, int parent_id)
1, Sam, null
2, Mike, 1
3, Mike, 1
4, Mike, 1
I need to make them like this
1, Sam, null
2, Mike#1, 1
3, Mike#2, 1
4, Mike#3, 1
And somehow add constraint. How can I do that?
sql postgresql
asked Nov 16 '18 at 18:51
Dennis GlotDennis Glot
54210
add a comment |
I have a hierarchical table with duplicate names on the same level. Example -
user (int id, string name, int parent_id)
1, Sam, null
2, Mike, 1
3, Mike, 1
4, Mike, 1
I need to make them like this
1, Sam, null
2, Mike#1, 1
3, Mike#2, 1
4, Mike#3, 1
And somehow add constraint. How can I do that?
sql postgresql
asked Nov 16 '18 at 18:51
Dennis GlotDennis Glot
54210
I have a hierarchical table with duplicate names on the same level. Example -
user (int id, string name, int parent_id)
1, Sam, null
2, Mike, 1
3, Mike, 1
4, Mike, 1
I need to make them like this
1, Sam, null
2, Mike#1, 1
3, Mike#2, 1
4, Mike#3, 1
And somehow add constraint. How can I do that?
sql postgresql
sql postgresql
asked Nov 16 '18 at 18:51
Dennis GlotDennis Glot
54210
asked Nov 16 '18 at 18:51
Dennis GlotDennis Glot
54210
edited Nov 17 '18 at 18:12
Dennis Glot
asked Nov 16 '18 at 18:51
Dennis GlotDennis Glot
54210
asked Nov 16 '18 at 18:51
Dennis GlotDennis Glot
54210
asked Nov 16 '18 at 18:51
Dennis GlotDennis Glot
54210
54210
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
You may use row_number() to generate those numbers in the sequence and COUNT analytic function to check whether or not to use sequence numbers
SELECT id,
CONCAT(name,
CASE
WHEN COUNT(*) OVER(
PARTITION BY name
) > 1 THEN --multiple names exist?
'#' || ROW_NUMBER() OVER(
PARTITION BY name
ORDER BY id )
END
) AS name, --else defaults to null (for single ones).
parent_id
FROM t
ORDER BY id;
It is not clear when you say "I need a hierarchical table", if you want to simply select them or create a new table. I would recommend you not to create another table simply to store mostly those same values, just create a VIEW instead using the above query as the base.
Demo
answered Nov 17 '18 at 5:37
Kaushik NayakKaushik Nayak
22k41332
Thank you) but i needed to change names in db.
– Dennis Glot
Nov 17 '18 at 18:10
add a comment |
First I needed to rename duplicates on the same level. Even in root directory where parent_id is null. This code will do
update user user_update set name = name || '#' || (
select user_count_ids.number
from (
select user_row_count.id id, row_number() over (order by user_row_count.id) number
from user user_row_count
where user_update.name = user_row_count.name and user_update.parent_id is not distinct from user_row_count.parent_id
) as user_count_ids
where user_count_ids.id = org_update.id
)
where (
select count(*) > 1
from user user_count
where user_update.name = user_count.name and user_update.parent_id is not distinct from user_count.parent_id
);
Then I needed some sort of constraint. Thanks to http://stackoverflow.com/a/8289253/5292928 I added this code
create unique index unique_name_parentId_when_parentId_is_not_null
on user (name, parent_id)
where parent_id is not null;
create unique index unique_name_when_parentId_is_null
on user (name)
where parent_id is null;
answered Nov 17 '18 at 18:08
Dennis GlotDennis Glot
54210
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%2f53343770%2fpostgresql-i-need-a-hierarchical-table-to-have-a-constraint-so-no-node-could-ha%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You may use row_number() to generate those numbers in the sequence and COUNT analytic function to check whether or not to use sequence numbers
SELECT id,
CONCAT(name,
CASE
WHEN COUNT(*) OVER(
PARTITION BY name
) > 1 THEN --multiple names exist?
'#' || ROW_NUMBER() OVER(
PARTITION BY name
ORDER BY id )
END
) AS name, --else defaults to null (for single ones).
parent_id
FROM t
ORDER BY id;
It is not clear when you say "I need a hierarchical table", if you want to simply select them or create a new table. I would recommend you not to create another table simply to store mostly those same values, just create a VIEW instead using the above query as the base.
Demo
answered Nov 17 '18 at 5:37
Kaushik NayakKaushik Nayak
22k41332
Thank you) but i needed to change names in db.
– Dennis Glot
Nov 17 '18 at 18:10
add a comment |
You may use row_number() to generate those numbers in the sequence and COUNT analytic function to check whether or not to use sequence numbers
SELECT id,
CONCAT(name,
CASE
WHEN COUNT(*) OVER(
PARTITION BY name
) > 1 THEN --multiple names exist?
'#' || ROW_NUMBER() OVER(
PARTITION BY name
ORDER BY id )
END
) AS name, --else defaults to null (for single ones).
parent_id
FROM t
ORDER BY id;
It is not clear when you say "I need a hierarchical table", if you want to simply select them or create a new table. I would recommend you not to create another table simply to store mostly those same values, just create a VIEW instead using the above query as the base.
Demo
answered Nov 17 '18 at 5:37
Kaushik NayakKaushik Nayak
22k41332
Thank you) but i needed to change names in db.
– Dennis Glot
Nov 17 '18 at 18:10
add a comment |
You may use row_number() to generate those numbers in the sequence and COUNT analytic function to check whether or not to use sequence numbers
SELECT id,
CONCAT(name,
CASE
WHEN COUNT(*) OVER(
PARTITION BY name
) > 1 THEN --multiple names exist?
'#' || ROW_NUMBER() OVER(
PARTITION BY name
ORDER BY id )
END
) AS name, --else defaults to null (for single ones).
parent_id
FROM t
ORDER BY id;
It is not clear when you say "I need a hierarchical table", if you want to simply select them or create a new table. I would recommend you not to create another table simply to store mostly those same values, just create a VIEW instead using the above query as the base.
Demo
answered Nov 17 '18 at 5:37
Kaushik NayakKaushik Nayak
22k41332
You may use row_number() to generate those numbers in the sequence and COUNT analytic function to check whether or not to use sequence numbers
SELECT id,
CONCAT(name,
CASE
WHEN COUNT(*) OVER(
PARTITION BY name
) > 1 THEN --multiple names exist?
'#' || ROW_NUMBER() OVER(
PARTITION BY name
ORDER BY id )
END
) AS name, --else defaults to null (for single ones).
parent_id
FROM t
ORDER BY id;
It is not clear when you say "I need a hierarchical table", if you want to simply select them or create a new table. I would recommend you not to create another table simply to store mostly those same values, just create a VIEW instead using the above query as the base.
Demo
answered Nov 17 '18 at 5:37
Kaushik NayakKaushik Nayak
22k41332
edited Nov 17 '18 at 5:47
answered Nov 17 '18 at 5:37
Kaushik NayakKaushik Nayak
22k41332
answered Nov 17 '18 at 5:37
Kaushik NayakKaushik Nayak
22k41332
answered Nov 17 '18 at 5:37
Kaushik NayakKaushik Nayak
22k41332
22k41332
Thank you) but i needed to change names in db.
– Dennis Glot
Nov 17 '18 at 18:10
add a comment |
Thank you) but i needed to change names in db.
– Dennis Glot
Nov 17 '18 at 18:10
Thank you) but i needed to change names in db.
– Dennis Glot
Nov 17 '18 at 18:10
Thank you) but i needed to change names in db.
– Dennis Glot
Nov 17 '18 at 18:10
add a comment |
First I needed to rename duplicates on the same level. Even in root directory where parent_id is null. This code will do
update user user_update set name = name || '#' || (
select user_count_ids.number
from (
select user_row_count.id id, row_number() over (order by user_row_count.id) number
from user user_row_count
where user_update.name = user_row_count.name and user_update.parent_id is not distinct from user_row_count.parent_id
) as user_count_ids
where user_count_ids.id = org_update.id
)
where (
select count(*) > 1
from user user_count
where user_update.name = user_count.name and user_update.parent_id is not distinct from user_count.parent_id
);
Then I needed some sort of constraint. Thanks to http://stackoverflow.com/a/8289253/5292928 I added this code
create unique index unique_name_parentId_when_parentId_is_not_null
on user (name, parent_id)
where parent_id is not null;
create unique index unique_name_when_parentId_is_null
on user (name)
where parent_id is null;
answered Nov 17 '18 at 18:08
Dennis GlotDennis Glot
54210
add a comment |
First I needed to rename duplicates on the same level. Even in root directory where parent_id is null. This code will do
update user user_update set name = name || '#' || (
select user_count_ids.number
from (
select user_row_count.id id, row_number() over (order by user_row_count.id) number
from user user_row_count
where user_update.name = user_row_count.name and user_update.parent_id is not distinct from user_row_count.parent_id
) as user_count_ids
where user_count_ids.id = org_update.id
)
where (
select count(*) > 1
from user user_count
where user_update.name = user_count.name and user_update.parent_id is not distinct from user_count.parent_id
);
Then I needed some sort of constraint. Thanks to http://stackoverflow.com/a/8289253/5292928 I added this code
create unique index unique_name_parentId_when_parentId_is_not_null
on user (name, parent_id)
where parent_id is not null;
create unique index unique_name_when_parentId_is_null
on user (name)
where parent_id is null;
answered Nov 17 '18 at 18:08
Dennis GlotDennis Glot
54210
add a comment |
First I needed to rename duplicates on the same level. Even in root directory where parent_id is null. This code will do
update user user_update set name = name || '#' || (
select user_count_ids.number
from (
select user_row_count.id id, row_number() over (order by user_row_count.id) number
from user user_row_count
where user_update.name = user_row_count.name and user_update.parent_id is not distinct from user_row_count.parent_id
) as user_count_ids
where user_count_ids.id = org_update.id
)
where (
select count(*) > 1
from user user_count
where user_update.name = user_count.name and user_update.parent_id is not distinct from user_count.parent_id
);
Then I needed some sort of constraint. Thanks to http://stackoverflow.com/a/8289253/5292928 I added this code
create unique index unique_name_parentId_when_parentId_is_not_null
on user (name, parent_id)
where parent_id is not null;
create unique index unique_name_when_parentId_is_null
on user (name)
where parent_id is null;
answered Nov 17 '18 at 18:08
Dennis GlotDennis Glot
54210
First I needed to rename duplicates on the same level. Even in root directory where parent_id is null. This code will do
update user user_update set name = name || '#' || (
select user_count_ids.number
from (
select user_row_count.id id, row_number() over (order by user_row_count.id) number
from user user_row_count
where user_update.name = user_row_count.name and user_update.parent_id is not distinct from user_row_count.parent_id
) as user_count_ids
where user_count_ids.id = org_update.id
)
where (
select count(*) > 1
from user user_count
where user_update.name = user_count.name and user_update.parent_id is not distinct from user_count.parent_id
);
Then I needed some sort of constraint. Thanks to http://stackoverflow.com/a/8289253/5292928 I added this code
create unique index unique_name_parentId_when_parentId_is_not_null
on user (name, parent_id)
where parent_id is not null;
create unique index unique_name_when_parentId_is_null
on user (name)
where parent_id is null;
answered Nov 17 '18 at 18:08
Dennis GlotDennis Glot
54210
edited Nov 20 '18 at 7:35
answered Nov 17 '18 at 18:08
Dennis GlotDennis Glot
54210
answered Nov 17 '18 at 18:08
Dennis GlotDennis Glot
54210
answered Nov 17 '18 at 18:08
Dennis GlotDennis Glot
54210
54210
add a comment |
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%2f53343770%2fpostgresql-i-need-a-hierarchical-table-to-have-a-constraint-so-no-node-could-ha%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
