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
instanceand 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
newtables 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
referencethese 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