Re: Copy entire schema A to a different schema B

On 2/20/19 4:21 PM, Tiffany Thang wrote:
> Hi Ron,
> How would that work if I'm only interested in importing/refreshing a
> single table in a target schema that contains several other tables?
>
> Thanks.
>
> Tiff
>
> On Wed, Feb 20, 2019 at 5:36 PM Rob Sargent <robjsargent(at)gmail(dot)com
> <mailto:robjsargent(at)gmail(dot)com>> wrote:
>
>
> On 2/20/19 3:22 PM, Tiffany Thang wrote:
>> Hi Adrian,
>> I managed to backup my table in parallel using -Fd but I'm back
>> to my original issue where I could not restore the table to a
>> different schema.
>>
>> For example,
>> I would like to backup testuser1.mytable and restore it to
>> testuser2.mytable.
>>
>> pg_dump -U testuser1 -Fd -f c:\temp\testuser1 -j 8 -t mytable -h
>> myserver testdb
>>
>> where mytable is in testuser1 schema
>>
>> The dump completed fine but when I attempted to restore the table
>> using pg_restore to another database, it tried to create the
>> table in testuser1 schema. The restore failed since testuser1
>> schema does not exist in the target database. When I created a
>> testuser1 schema in the target database, the restore worked fine.
>> Since the dump toc is in binary format, I could not make the
>> change to reflect the new target schema, testuser2.
>>
>> So, how should I go about restoring tables from one schema to a
>> different schema name?
>>
>> Thanks.
>>
>> Tiff
>>
>>
> Can you not simply rename the schema after importing it? Perhaps
> renaming an exiting version first, if necessary. Rename schema A
> to B; import schema A; rename A to C; rename B to A;
>
>
If nothing is in the way for the restore maybe all you need is

ALTER TABLE [ IF EXISTS ]/|name|/
SET SCHEMA/|new_schema|/