Re: Moving Specific Data Across Schemas Including FKs

From: Cory Tucker <cory(dot)tucker(at)gmail(dot)com>
To: Steve Atkins <steve(at)blighty(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Moving Specific Data Across Schemas Including FKs
Date: 2015-04-23 17:47:19
Message-ID: CAG_=8kBycZ8MxFVhnSN5RWWJ=pgRDY1-ga7OAyzk0gntZ4Zwwg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Thu, Apr 23, 2015 at 10:27 AM Steve Atkins <steve(at)blighty(dot)com> wrote:

>
> On Apr 23, 2015, at 10:09 AM, Cory Tucker <cory(dot)tucker(at)gmail(dot)com> wrote:
>
> > I have the need to move a specific set of data from one schema to
> another. These schemas are on the same database instance and have all of
> the same relations defined. The SQL to copy data from one table is
> relatively straightforward:
> >
> > INSERT INTO schema_b.my_table
> > SELECT * FROM schema_a.my_table WHERE ...
>
> Would ALTER TABLE ... SET SCHEMA do what you need? A schema is mostly just
> a name space, so there's no need to create new tables or copy data around.
>
> Cheers,
> Steve
>

If I were moving all of the contents from these table(s) then it might
work, but I only want to move a specific selection of it based on a where
clause.

--Cory

>
> >
> > What I am trying to figure out is that if I also have other relations
> that have foreign keys into the data I am moving, how would I also move the
> data from those relations and maintain the FK integrity?
> >
> > The tables are setup to use BIGSERIAL values for the id column which is
> the primary key, and the foreign keys reference these id columns. Ideally
> each schema would use it's own serial for the ID values, but I'm open to
> clever alternatives.
> >
> > I am using PG 9.3.5 (Amazon RDS), but with an option to move to 9.4
> should the situation call for it.
> >
> > thanks
> > --Cory
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

In response to

Browse pgsql-general by date

  From Date Subject
Next Message William Dunn 2015-04-23 17:52:53 Re: R: DB on mSATA SSD
Previous Message Steve Atkins 2015-04-23 17:22:56 Re: Moving Specific Data Across Schemas Including FKs