Moving Specific Data Across Schemas Including FKs

On Apr 23, 2015, at 10:09 AM, Cory Tucker <cory.tucker@gmail.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

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Apr 23, 2015 at 10:27 AM Steve Atkins <steve@blighty.com> wrote:

On Apr 23, 2015, at 10:09 AM, Cory Tucker <cory.tucker@gmail.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

On 23/04/2015 18:09, Cory Tucker 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 ...

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?

I'd create the tables in the new schema without the FK constraints, copy
the data, then add the constraints afterwards.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 23/04/2015 19:08, Raymond O'Donnell wrote:

On 23/04/2015 18:09, Cory Tucker 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 ...

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?

I'd create the tables in the new schema without the FK constraints, copy
the data, then add the constraints afterwards.

Meant to add, you'll also need to do

select setval(...);

on the sequence(s) in the new schema supplying the ID values, to set
them to something higher than any extant values copied in from the old
schema... but I'm sure you thought of that. :-)

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 4/23/15 1:08 PM, Raymond O'Donnell wrote:

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?

I'd create the tables in the new schema without the FK constraints, copy
the data, then add the constraints afterwards.

You could also deffer the constraints, but that's probably going to be a
lot slower.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general