How can I export data from one schema to insert on another schema with the same structure

Question

I want to move all my data on my 'test1' schema on all table where id > 5000

So, I did export 'test1' schema and create new temp schema name 'test1_new' then I do this on all table

INSERT INTO test1_new.{table_name} SELECT * FROM test1.{table_name}

then all data I want are moved to 'test1_new' then I renamed them

test1 --- to ----> test1_old

test1_new --- to ----> test1

then I do

pg_dump -a -f test1.data -F c -n test1 -v -U myuser mydatabase

so, I get test1.data which contains all data I want to move

then I move to my main database by using pg_restore

pg_restore -a --host=192.168.1.2 --port=4444 --username=myuser --password --dbname=mydatabase --role=myuser -c /mnt/databackup/test1.data

and I got this error

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 94497; 0 164460306 TABLE DATA assign_suggestions sodb
pg_restore: [archiver (db)] COPY failed for table "assign_suggestions": ERROR:  duplicate key value violates unique constraint "assign_suggestions_pkey"
DETAIL:  Key (id)=(16404) already exists.
CONTEXT:  COPY assign_suggestions, line 1
pg_restore: [archiver (db)] Error from TOC entry 94503; 0 164460330 TABLE DATA assigns sodb
pg_restore: [archiver (db)] COPY failed for table "assigns": ERROR:  duplicate key value violates unique constraint "assigns_pkey"
DETAIL:  Key (id)=(16664) already exists.
CONTEXT:  COPY assigns, line 1

and a lot like this

What I did wrong ? In my main database there are some record in it already, but i'm sure the id is not duplicate.

How can I solved this? I also tried create a clone schema with no constraint but I still got error like this when I tried pg_restore it.. and I have no idea what happens..

Is there anyway else I can move my data on all table in schema with condition where id > 5000 to my main using database?

score 0 · Answer 1 · answered Jul 22 '14 at 09:35

Instead of:

pg_restore -a --host=192.168.1.2 --port=4444 --username=myuser --password --  dbname=mydatabase --role=myuser -c /mnt/databackup/test1.data

use:

pg_restore -a --host=192.168.1.2 --port=4444 --username=myuser --password --  dbname=mydatabase --role=myuser -c /mnt/databackup/test1.data --schema=*YOUR TARGET SCHEMA NAME*

How can I export data from one schema to insert on another schema with the same structure

1 Answers1