postgres sql Insert into

Asked

Viewed 90 times

1

I have to copy data from two databases to a new one, it happens that when copied new data these already exist but there are new ones that are not inserted.

The query I have at the moment is this: 

INSERT INTO concelho(id,censo,concelho)
SELECT "CONCELHO","CENSO","CONCELHO_DSG"
FROM dblink('dbname=meu_db host=meu_host user=meu_user password=minha_senha' ::text,'SELECT "CONCELHO","CENSO","CONCELHO_DSG" FROM "MUNICIPIO (Concelho)"' ::text,false) 
as tabela_temp("CONCELHO" varchar, "CENSO" varchar, "CONCELHO_DSG" character varying (200))
postgresql
  • 1

    Hi user, while a dblink to localhost is generally safe (it is not exposed on the Internet), be careful not to post users and passwords here. Remember that content is indexed by search engines and is open on the Internet.

  • 1

    I don’t understand. What’s the problem? Duplicate data is entered? Or are there data that is simply ignored? There is an error message?

  • yes it checks that there is already an equal record, but it does not put the different

2 answers

1

already solved 

'INSERT INTO concelho(id,censo,concelho)´SELECT "CONCELHO","CENSO","CONCELHO_DSG"FROM dblink('dbname=meu_db host=meu_hostuser=meu_user password=minha_senha'::text,'SELECT"CONCELHO","CENSO","CONCELHO_DSG" FROM"MUNICIPIO (Concelho)"' ::text,false) as tabela_temp("CONCELHO" varchar,"CENSO" varchar, "CONCELHO_DSG" character varying (200))WHERE    NOT EXISTS (SELECT id,censo,concelho FROM concelho WHERE id = tabela_temp."CONCELHO" 
);'

0

insert into concelho(id,censo,concelho)
select "CONCELHO","CENSO","CONCELHO_DSG"
from dblink(
    'dbname=meu_db host=meu_host user=meu_user password=minha_senha' ::text,
    'SELECT "CONCELHO","CENSO","CONCELHO_DSG" FROM "MUNICIPIO (Concelho)"' ::text,false)
as tabela_temp("CONCELHO" varchar, "CENSO" varchar, "CONCELHO_DSG" character varying (200))
except
select "CONCELHO","CENSO","CONCELHO_DSG"
from concelho

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.