Hi,
In my database, I created a lot of tables before I found out about
schemas. It's a mess!
Suppose I want to get organised, and create a couple of schemas. How do
I move existing tables into my newly created schemas?
e.g.
public.table1 -> mynewschema1.ta ble1
public.table2 -> mynewschema2.ta ble2
?
Thanks.
Max
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org 5  1826 
On Friday 30 Jul 2004 3:56 pm, m.********@lse. ac.uk wrote: Hi,
In my database, I created a lot of tables before I found out about
schemas. It's a mess!
Suppose I want to get organised, and create a couple of schemas. How do
I move existing tables into my newly created schemas?
e.g.
public.table1 -> mynewschema1.ta ble1
public.table2 -> mynewschema2.ta ble2
You can dump the tables, edit the dump to alter the create table statement so
that the table is created in proper schema.
This could be very rudimentary depending upon size of each table and number of
tables. The local documentation of 7.4.3 indicates that either alter table or
alter schema does not support moving tables between schemas.
You could resort to catalog hacking and change the schema manually but I am
not sure how advisable and predictable it would be.
HTH
Shridhar
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
You may be able to play around with the system catalogs, but I don't
know anything about that...
An easy method of moving the tables are
create table mynewschema1.ta ble1 as select * from public.table1;
drop public.table1;
create table mynewschema2.ta ble2as select * from public.table2;
drop public.table2;
You may need to recreate any indexes that the tables used in the public
schema, and you may need to grant rights in order to allow your users to
access the tables...
Hope that helps
John Sidney-Woollett
create m.********@lse. ac.uk wrote:
Hi,
In my database, I created a lot of tables before I found out about
schemas. It's a mess!
Suppose I want to get organised, and create a couple of schemas. How do
I move existing tables into my newly created schemas?
e.g.
public.table1 -> mynewschema1.ta ble1
public.table2 -> mynewschema2.ta ble2
?
Thanks.
Max
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
Thanks. I hadn't thought of that - that's probably easiest.
On Fri, 2004-07-30 at 10:19, John Sidney-Woollett wrote: You may be able to play around with the system catalogs, but I don't
know anything about that...
An easy method of moving the tables are
create table mynewschema1.ta ble1 as select * from public.table1;
drop public.table1;
create table mynewschema2.ta ble2as select * from public.table2;
drop public.table2;
You may need to recreate any indexes that the tables used in the public
schema, and you may need to grant rights in order to allow your users to
access the tables...
Hope that helps
John Sidney-Woollett
create
m.********@lse. ac.uk wrote:
Hi,
In my database, I created a lot of tables before I found out about
schemas. It's a mess!
Suppose I want to get organised, and create a couple of schemas. How do
I move existing tables into my newly created schemas?
e.g.
public.table1 -> mynewschema1.ta ble1
public.table2 -> mynewschema2.ta ble2
?
Thanks.
Max
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Be careful with any objects (functions, triggers) to make sure that are
updated to reference the new tables in their correct schemas too.
JOhn Sidney-Woollett m.********@lse. ac.uk wrote: Thanks. I hadn't thought of that - that's probably easiest.
On Fri, 2004-07-30 at 10:19, John Sidney-Woollett wrote:
You may be able to play around with the system catalogs, but I don't
know anything about that...
An easy method of moving the tables are
create table mynewschema1.ta ble1 as select * from public.table1;
drop public.table1;
create table mynewschema2.ta ble2as select * from public.table2;
drop public.table2;
You may need to recreate any indexes that the tables used in the public
schema, and you may need to grant rights in order to allow your users to
access the tables...
Hope that helps
John Sidney-Woollett
create
m.********@ls e.ac.uk wrote:
Hi,
In my database, I created a lot of tables before I found out about
schemas. It's a mess!
Suppose I want to get organised, and create a couple of schemas. How do
I move existing tables into my newly created schemas?
e.g.
public.table 1 -> mynewschema1.ta ble1
public.table 2 -> mynewschema2.ta ble2
?
Thanks.
Max
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
On Friday 30 Jul 2004 3:49 pm, John Sidney-Woollett wrote: You may be able to play around with the system catalogs, but I don't
know anything about that...
An easy method of moving the tables are
create table mynewschema1.ta ble1 as select * from public.table1;
drop public.table1;
create table mynewschema2.ta ble2as select * from public.table2;
drop public.table2;
One can also create a table before hand using 'create table like' and then
inserting rows. This has advantage of preserving any constraints in original
table.
But this does not take care of indexes AFAIK.
Shridhar
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: joshsackett |
last post by:
Hi all,
I have 5 databases, each about 20GB in size. I need to copy the data to
a new server for reporting purposes. Initially I wanted to setup
transactional replication; however, the database schemas often change
(adding or modifying tables) and in order to accomodate those changes
through replication I would need to send a new snapshot each time a
change occurs. This is very time consuming.
So I decided instead to use log shipping,...
|
by: btober |
last post by:
Whenever I create a temporary table, with something like
CREATE TEMPORARY TABLE temptable1 AS SELECT * FROM paid.ad_hoc_query;
New schemas appear, with names like "pg_temp_1". I guess the appearance
of these schemas with "temp" in the name indicates that they are
"temporary" schemas and related to the temporary table creation, but the
schemas persist even after the end of the session in which the temporary
table was created.
|
by: Karam Chand |
last post by:
Hello
I have been working with Access and MySQL for pretty
long time. Very simple and able to perform their jobs.
I dont need to start a flame anymore :)
I have to work with PGSQL for my companies current
project.
I have been able to setup postgresql in my rh box and
|
by: db2dude |
last post by:
Hello all,
I have a database around 600GB and all tables were self-created by the
application. Hence, we have only one huge SMS tablespace where all the
tables are stored. To get better performance, we are planning to move
the tables to a DMS tablespace with multiple containers. There are LOTS
of tables and some tables have almost 40GB of data. Can you please
suggest and efficient way of doing this.
Also, I know that even a 32KB page...
|
by: Gladiator |
last post by:
hi everyone.....
Can anyone tell how to move tables between
schemas.......... that is changing the schema name of the
table................
thanks in advance....
Kamal
| | |
by: fuzzybr80 |
last post by:
I am using MySQL 5.0 with a number of innodb tables whose ibdata files
are growing quite quickly and filling up the /var partition (file is
/var/mysql/ibdata1). Earlier on I followed instructions in the docs to
create a new ibdata file on a bigger partition
/disk2/var/mysql/ibdata2) and set that to autoextend instead.
However I would like to reclaim the disk space on /var by moving the
existing ibdata1 file to /disk2/var/mysql/ibdata1....
|
by: nyathancha |
last post by:
Hi,
I have a question regarding best practices in database design. In a
relational database, is it wise/necessary to sometimes create tables
that are not related to other tables through a foreign Key
relationship or does this always indicate some sort of underlying
design flaw. Something that requires a re evaluation of the problem
domain?
The reason I ask is because in our application, the user can perform x
|
by: Gary |
last post by:
Hello guys!
Bear with me, I am a newbie.
She is the Data Warehouse manager. She has about 50 users to use the
Oracle database from M$ Access via ODBC connection. All those users
have only SELECT privileges on certain tables. I built all the roles
and users for them and they work fine.
Then she asked "Why do YOU let them see all those system tables?",
|
by: JB |
last post by:
Hi All,
I'm writing an application that connects to various databases via
ODBC.
I'd like to get a list of all the available Schemas and Tables in a
Database. It looks like the GetSchema function is what I need, however
there seem to be slight differences between various drivers.
For instance when calling GetSchema("Tables") the SQL Server ODBC
driver returns the following columns:
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
