create (or copy) table schema using postgres_fdw or dblink
I have many tables in different databases and want to bring them to a database.
It seems like I have to create foreign table in the database (where I want to merge them all) with schemas of all the tables.
I am sure, there is a way to automate this (by the way, I am going to use psql command) but I do not know where to start.
what I have found so far is I can use
select * from information_schema.columns
where table_schema = 'public' and table_name = 'mytable'
I added more detail explanation.
- I wanted to copy tables from another database
- the tables have same column names and data type
- using postgres_fdw, I needed to set up a field name and data type for each tables (the table names are also same)
- then, I want to union the tables have same name all to have one single table.
for that, I am going to add prefix on table
for instance, mytable in db1, mytable in db2, mytable in db3 as in
db1_mytable, db2_mytable, db3_mytable in my local database.
Thanks to Albe's comment, I managed it and now I need to figure out doing 4th step using psql command.
postgresql psql dblink
asked Nov 16 '18 at 4:56
Pil KwonPil Kwon
1037
add a comment |
I have many tables in different databases and want to bring them to a database.
It seems like I have to create foreign table in the database (where I want to merge them all) with schemas of all the tables.
I am sure, there is a way to automate this (by the way, I am going to use psql command) but I do not know where to start.
what I have found so far is I can use
select * from information_schema.columns
where table_schema = 'public' and table_name = 'mytable'
I added more detail explanation.
- I wanted to copy tables from another database
- the tables have same column names and data type
- using postgres_fdw, I needed to set up a field name and data type for each tables (the table names are also same)
- then, I want to union the tables have same name all to have one single table.
for that, I am going to add prefix on table
for instance, mytable in db1, mytable in db2, mytable in db3 as in
db1_mytable, db2_mytable, db3_mytable in my local database.
Thanks to Albe's comment, I managed it and now I need to figure out doing 4th step using psql command.
postgresql psql dblink
asked Nov 16 '18 at 4:56
Pil KwonPil Kwon
1037
Methinks you are looking forIMPORT FOREIGN SCHEMA, but your description is too vague to be sure.
– Laurenz Albe
Nov 16 '18 at 5:00
Ah! right, that is the answer. but it imports all the records. I wanted to have only field (column) names though.
– Pil Kwon
Nov 16 '18 at 5:21
You still didn't explain what you need so that I can understand it.
– Laurenz Albe
Nov 16 '18 at 5:44
Ah, you want to shard data across several databases and use postgres_fdw and aUNIONview for that, and you want to automatize the procedure. I suggest that you use declarative partitioning for the latter.
– Laurenz Albe
Nov 16 '18 at 7:04
add a comment |
I have many tables in different databases and want to bring them to a database.
It seems like I have to create foreign table in the database (where I want to merge them all) with schemas of all the tables.
I am sure, there is a way to automate this (by the way, I am going to use psql command) but I do not know where to start.
what I have found so far is I can use
select * from information_schema.columns
where table_schema = 'public' and table_name = 'mytable'
I added more detail explanation.
- I wanted to copy tables from another database
- the tables have same column names and data type
- using postgres_fdw, I needed to set up a field name and data type for each tables (the table names are also same)
- then, I want to union the tables have same name all to have one single table.
for that, I am going to add prefix on table
for instance, mytable in db1, mytable in db2, mytable in db3 as in
db1_mytable, db2_mytable, db3_mytable in my local database.
Thanks to Albe's comment, I managed it and now I need to figure out doing 4th step using psql command.
postgresql psql dblink
asked Nov 16 '18 at 4:56
Pil KwonPil Kwon
1037
I have many tables in different databases and want to bring them to a database.
It seems like I have to create foreign table in the database (where I want to merge them all) with schemas of all the tables.
I am sure, there is a way to automate this (by the way, I am going to use psql command) but I do not know where to start.
what I have found so far is I can use
select * from information_schema.columns
where table_schema = 'public' and table_name = 'mytable'
I added more detail explanation.
- I wanted to copy tables from another database
- the tables have same column names and data type
- using postgres_fdw, I needed to set up a field name and data type for each tables (the table names are also same)
- then, I want to union the tables have same name all to have one single table.
for that, I am going to add prefix on table
for instance, mytable in db1, mytable in db2, mytable in db3 as in
db1_mytable, db2_mytable, db3_mytable in my local database.
Thanks to Albe's comment, I managed it and now I need to figure out doing 4th step using psql command.
postgresql psql dblink
postgresql psql dblink
asked Nov 16 '18 at 4:56
Pil KwonPil Kwon
1037
asked Nov 16 '18 at 4:56
Pil KwonPil Kwon
1037
edited Nov 16 '18 at 6:10
Pil Kwon
asked Nov 16 '18 at 4:56
Pil KwonPil Kwon
1037
asked Nov 16 '18 at 4:56
Pil KwonPil Kwon
1037
asked Nov 16 '18 at 4:56
Pil KwonPil Kwon
1037
1037
Methinks you are looking forIMPORT FOREIGN SCHEMA, but your description is too vague to be sure.
– Laurenz Albe
Nov 16 '18 at 5:00
Ah! right, that is the answer. but it imports all the records. I wanted to have only field (column) names though.
– Pil Kwon
Nov 16 '18 at 5:21
You still didn't explain what you need so that I can understand it.
– Laurenz Albe
Nov 16 '18 at 5:44
Ah, you want to shard data across several databases and use postgres_fdw and aUNIONview for that, and you want to automatize the procedure. I suggest that you use declarative partitioning for the latter.
– Laurenz Albe
Nov 16 '18 at 7:04
add a comment |
Methinks you are looking forIMPORT FOREIGN SCHEMA, but your description is too vague to be sure.
– Laurenz Albe
Nov 16 '18 at 5:00
Ah! right, that is the answer. but it imports all the records. I wanted to have only field (column) names though.
– Pil Kwon
Nov 16 '18 at 5:21
You still didn't explain what you need so that I can understand it.
– Laurenz Albe
Nov 16 '18 at 5:44
Ah, you want to shard data across several databases and use postgres_fdw and aUNIONview for that, and you want to automatize the procedure. I suggest that you use declarative partitioning for the latter.
– Laurenz Albe
Nov 16 '18 at 7:04
Methinks you are looking for
IMPORT FOREIGN SCHEMA, but your description is too vague to be sure.– Laurenz Albe
Nov 16 '18 at 5:00
Methinks you are looking for
IMPORT FOREIGN SCHEMA, but your description is too vague to be sure.– Laurenz Albe
Nov 16 '18 at 5:00
Ah! right, that is the answer. but it imports all the records. I wanted to have only field (column) names though.
– Pil Kwon
Nov 16 '18 at 5:21
Ah! right, that is the answer. but it imports all the records. I wanted to have only field (column) names though.
– Pil Kwon
Nov 16 '18 at 5:21
You still didn't explain what you need so that I can understand it.
– Laurenz Albe
Nov 16 '18 at 5:44
You still didn't explain what you need so that I can understand it.
– Laurenz Albe
Nov 16 '18 at 5:44
Ah, you want to shard data across several databases and use postgres_fdw and a
UNION view for that, and you want to automatize the procedure. I suggest that you use declarative partitioning for the latter.– Laurenz Albe
Nov 16 '18 at 7:04
Ah, you want to shard data across several databases and use postgres_fdw and a
UNION view for that, and you want to automatize the procedure. I suggest that you use declarative partitioning for the latter.– Laurenz Albe
Nov 16 '18 at 7:04
add a comment |
0
active
oldest
votes
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%2f53331685%2fcreate-or-copy-table-schema-using-postgres-fdw-or-dblink%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 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%2f53331685%2fcreate-or-copy-table-schema-using-postgres-fdw-or-dblink%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
Methinks you are looking for
IMPORT FOREIGN SCHEMA, but your description is too vague to be sure.– Laurenz Albe
Nov 16 '18 at 5:00
Ah! right, that is the answer. but it imports all the records. I wanted to have only field (column) names though.
– Pil Kwon
Nov 16 '18 at 5:21
You still didn't explain what you need so that I can understand it.
– Laurenz Albe
Nov 16 '18 at 5:44
Ah, you want to shard data across several databases and use postgres_fdw and a
UNIONview for that, and you want to automatize the procedure. I suggest that you use declarative partitioning for the latter.– Laurenz Albe
Nov 16 '18 at 7:04