0

I have two schema's in one database

1) public
2) test

In public i have list of external tables like

ext_table1,ext_table_2,ext_table_3

How do i copy those tables to test schema from public in the same database?

user8545255
  • 761
  • 3
  • 9
  • 21
  • Why "copy" them to begin with? Can't you simply create views in the test schema that select from the external tables in the public schema? –  Aug 13 '20 at 14:18
  • I am trying to move them to test schema. Once we got them moved, will be dropping the tables from public schema. Goal is not to have any tables under public schema – user8545255 Aug 13 '20 at 14:22
  • What is an 'external' table? Do you mean foreign table? – jjanes Aug 13 '20 at 15:01

1 Answers1

0

If you want to move them, then just do that:

alter table ext_table1 set schema test;
  • What if we have 100 tables that needs to move. Could we alter all at once? – user8545255 Aug 13 '20 at 14:26
  • 1
    Use PL/pgSQL and dynamic SQL or generate the statements using SQL then use `psql`'s `\gexec` to run the generated SQL. There are tons of examples out there on how you automate things like that –  Aug 13 '20 at 14:28