Update primary keys in multiple tables using PostgreSQL
I have 11 tables in a single schema in PostgreSQL. Each table has a column named 'id' that should be a unique primary key. Unfortunately, some of the tables have ids that are in other tables.
How can I create a new sequence and update all the tables' id values with fully unique values? Do I have to do it one sequence and one table at a time?
I'm new at SQL, so I don't know where to start beyond creating a sequence and updating a single table.
postgresql
asked Nov 15 '18 at 0:36
BirdBird
7023934
add a comment |
I have 11 tables in a single schema in PostgreSQL. Each table has a column named 'id' that should be a unique primary key. Unfortunately, some of the tables have ids that are in other tables.
How can I create a new sequence and update all the tables' id values with fully unique values? Do I have to do it one sequence and one table at a time?
I'm new at SQL, so I don't know where to start beyond creating a sequence and updating a single table.
postgresql
asked Nov 15 '18 at 0:36
BirdBird
7023934
add a comment |
I have 11 tables in a single schema in PostgreSQL. Each table has a column named 'id' that should be a unique primary key. Unfortunately, some of the tables have ids that are in other tables.
How can I create a new sequence and update all the tables' id values with fully unique values? Do I have to do it one sequence and one table at a time?
I'm new at SQL, so I don't know where to start beyond creating a sequence and updating a single table.
postgresql
asked Nov 15 '18 at 0:36
BirdBird
7023934
I have 11 tables in a single schema in PostgreSQL. Each table has a column named 'id' that should be a unique primary key. Unfortunately, some of the tables have ids that are in other tables.
How can I create a new sequence and update all the tables' id values with fully unique values? Do I have to do it one sequence and one table at a time?
I'm new at SQL, so I don't know where to start beyond creating a sequence and updating a single table.
postgresql
postgresql
asked Nov 15 '18 at 0:36
BirdBird
7023934
asked Nov 15 '18 at 0:36
BirdBird
7023934
asked Nov 15 '18 at 0:36
BirdBird
7023934
asked Nov 15 '18 at 0:36
BirdBird
7023934
asked Nov 15 '18 at 0:36
BirdBird
7023934
7023934
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Your question is a bit vague but I assume your ids are of type int/bigint. And you want these 'id's to be globally unique not just inside one table.
There might be many options. This one is one of the most simple but maybe not the most elegant way. First create a sequence to provied ids, like:
CREATE SEQUENCE globally_unique_id;
Then just go through all your 11 tables with update, like:
UPDATE table1 SET id = nextval('globally_unique_id');
Using this one sequence will not give you the same id for any rows in all tables.
If you have foreign key constraints in other tables to the 'id' column that are not declared to cascade on update you are in bigger trouble. But that is an another story.
Of course you do not need to do it table by table. You could for example query Postgres system tables for all tables - like asked here - (or make an array of table names by hand) and loop tables with id in some specially constructed query or procedure.
But in my opinion: 'only' 11 tables might not be worth of coding it. On the other hand it might be useful later if you need to do the same again.
answered Nov 15 '18 at 17:57
pirhopirho
5,071111932
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%2f53310836%2fupdate-primary-keys-in-multiple-tables-using-postgresql%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
Your question is a bit vague but I assume your ids are of type int/bigint. And you want these 'id's to be globally unique not just inside one table.
There might be many options. This one is one of the most simple but maybe not the most elegant way. First create a sequence to provied ids, like:
CREATE SEQUENCE globally_unique_id;
Then just go through all your 11 tables with update, like:
UPDATE table1 SET id = nextval('globally_unique_id');
Using this one sequence will not give you the same id for any rows in all tables.
If you have foreign key constraints in other tables to the 'id' column that are not declared to cascade on update you are in bigger trouble. But that is an another story.
Of course you do not need to do it table by table. You could for example query Postgres system tables for all tables - like asked here - (or make an array of table names by hand) and loop tables with id in some specially constructed query or procedure.
But in my opinion: 'only' 11 tables might not be worth of coding it. On the other hand it might be useful later if you need to do the same again.
answered Nov 15 '18 at 17:57
pirhopirho
5,071111932
add a comment |
Your question is a bit vague but I assume your ids are of type int/bigint. And you want these 'id's to be globally unique not just inside one table.
There might be many options. This one is one of the most simple but maybe not the most elegant way. First create a sequence to provied ids, like:
CREATE SEQUENCE globally_unique_id;
Then just go through all your 11 tables with update, like:
UPDATE table1 SET id = nextval('globally_unique_id');
Using this one sequence will not give you the same id for any rows in all tables.
If you have foreign key constraints in other tables to the 'id' column that are not declared to cascade on update you are in bigger trouble. But that is an another story.
Of course you do not need to do it table by table. You could for example query Postgres system tables for all tables - like asked here - (or make an array of table names by hand) and loop tables with id in some specially constructed query or procedure.
But in my opinion: 'only' 11 tables might not be worth of coding it. On the other hand it might be useful later if you need to do the same again.
answered Nov 15 '18 at 17:57
pirhopirho
5,071111932
add a comment |
Your question is a bit vague but I assume your ids are of type int/bigint. And you want these 'id's to be globally unique not just inside one table.
There might be many options. This one is one of the most simple but maybe not the most elegant way. First create a sequence to provied ids, like:
CREATE SEQUENCE globally_unique_id;
Then just go through all your 11 tables with update, like:
UPDATE table1 SET id = nextval('globally_unique_id');
Using this one sequence will not give you the same id for any rows in all tables.
If you have foreign key constraints in other tables to the 'id' column that are not declared to cascade on update you are in bigger trouble. But that is an another story.
Of course you do not need to do it table by table. You could for example query Postgres system tables for all tables - like asked here - (or make an array of table names by hand) and loop tables with id in some specially constructed query or procedure.
But in my opinion: 'only' 11 tables might not be worth of coding it. On the other hand it might be useful later if you need to do the same again.
answered Nov 15 '18 at 17:57
pirhopirho
5,071111932
Your question is a bit vague but I assume your ids are of type int/bigint. And you want these 'id's to be globally unique not just inside one table.
There might be many options. This one is one of the most simple but maybe not the most elegant way. First create a sequence to provied ids, like:
CREATE SEQUENCE globally_unique_id;
Then just go through all your 11 tables with update, like:
UPDATE table1 SET id = nextval('globally_unique_id');
Using this one sequence will not give you the same id for any rows in all tables.
If you have foreign key constraints in other tables to the 'id' column that are not declared to cascade on update you are in bigger trouble. But that is an another story.
Of course you do not need to do it table by table. You could for example query Postgres system tables for all tables - like asked here - (or make an array of table names by hand) and loop tables with id in some specially constructed query or procedure.
But in my opinion: 'only' 11 tables might not be worth of coding it. On the other hand it might be useful later if you need to do the same again.
answered Nov 15 '18 at 17:57
pirhopirho
5,071111932
answered Nov 15 '18 at 17:57
pirhopirho
5,071111932
answered Nov 15 '18 at 17:57
pirhopirho
5,071111932
answered Nov 15 '18 at 17:57
pirhopirho
5,071111932
5,071111932
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%2f53310836%2fupdate-primary-keys-in-multiple-tables-using-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
