Inserting into postgres database

Question

I am working trying to write an insert query into a backup database. I writing place and entities tables into this database. The issue is entities is linked to place via place.id column. I added a column place.original_id in the place table to store it's original 'id'. so now that i entered place into the new database it's id column changed but i have the original id stored so I can still link entities table to it. I am trying to figure out how to write entities to get the new id

so far i am at this point:

insert into entities_backup (id, place_id)
select 
  nextval('public.entities_backup_id_seq'),
  (select id from places where original_id = (select place_id from entities) as place_id
 from
   entities

I know I am missing something because this does not work. I need to grab the id column from places when entity.place_id = places.original_id. Any help would be great.

Have you considered using Postgres's built in backup and restore mechanisms? And is that semicolon supposed to be in there? — Schwern
– Schwern, Commented Oct 20, 2016 at 17:45
no its suppose to be a comma. I am actually copying these tables to this postgres database and storing. Its a onetime project. The original data is in a oracle table, i just need to bring it to postgres so I can use it with another project with out messing up the original data. That is why the ID columns do not match perfectly. — Kyle K
– Kyle K, Commented Oct 20, 2016 at 17:48
How did you do the Oracle dump? Did both places and entities come over from Oracle? — Schwern
– Schwern, Commented Oct 20, 2016 at 17:59
yes, i dropped them to a csv file and then imported the csv files into temp tables in postgres. Then taking the csv dumps into the active tables in postgres. — Kyle K
– Kyle K, Commented Oct 20, 2016 at 18:10

Julia Leder · Accepted Answer · 2016-10-20 18:03:19Z

1

I think this is what you want

insert into entities_backup (id, place_id)
select nextval('public.entities_backup_id_seq'), places.id 
from places, entities
where places.original_id = entities.place_id;

answered Oct 20, 2016 at 18:03

Julia Leder

8265 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Schwern · Accepted Answer · 2016-10-20 18:22:07Z

I am working trying to write an insert query into a backup database. I writing place and entities tables into this database. The issue is entities is linked to place via place.id column. I added a column place.original_id in the place table to store it's original 'id'. so now that i entered place into the new database it's id column changed but i have the original id stored so I can still link entities table to it.

It would be simpler to not have this problem in the first place.

Rather than trying to fix this up after the fact, the better solution is to dump and load places and entities complete with their primary and foreign keys intact. Oracle's EXPORT or a utility such as ora2pg should be able to do it.

Sorry I can't say more. I know Postgres, not Oracle.

Collectives™ on Stack Overflow

Inserting into postgres database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related