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.

Answer 1

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.

Answer 2

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;