Postgresql, Copy data to a new table from a foreign table

1.6k views Asked by At

I'm trying to do a job that will copy data from a foreign table called "m_aduana" of the schema "nathalia" to my schema "publico" and my table "mae_aduana".

I need to do a query that copies all the values from the table "m_aduana" avoiding duplicates.

I got something like this for now but the result sends me an Insert 0 0, which means nothing is inserted.

insert into publico.mae_aduana(cod_aduana,nom_aduana,des_aduana,cod_aduana1,cod_aduana2,cod_aduana3,est_aduana)
select cod_aduana,nom_aduana,des_aduana,cod_aduana1,cod_aduana2,cod_aduana3,est_aduana 
    from nathalia.m_aduana
    where not exists (
    select * from publico.mae_aduana ma_ad, nathalia.m_aduana m_ad
        where ma_ad.cod_aduana = m_ad.cod_aduana)
2

There are 2 answers

1
RafaelCaballero On BEST ANSWER

I think you have an error in the inner select. You don't need to use again the table nathalia.m_aduana. If should be something like:

insert into publico.mae_aduana(cod_aduana,nom_aduana,des_aduana,cod_aduana1,cod_aduana2,cod_aduana3,est_aduana)
select cod_aduana,nom_aduana,des_aduana,cod_aduana1,cod_aduana2,cod_aduana3,est_aduana 
    from nathalia.m_aduana
    where not exists (
    select * from publico.mae_aduana ma_ad
        where ma_ad.cod_aduana = nathalia.m_aduana.cod_aduana)
0
Rahul On

You might want to change the where exists part like below

from nathalia.m_aduana m
where not exists (
select 1 from publico.mae_aduana
    where cod_aduana = m.cod_aduana)