Search code examples
postgresqlliquibaseliquibase-sql

Liquibase insert select where not exists

I want to insert into table1 multiple rows from table2. The problem is that I have a field of same name in table2 and table1 and I don't want to insert data if there's already a record with same value in this field. Now I have something like this:

insert into table1 (id, sameField, constantField, superFied)
select gen_random_uuid(), "sameField", 'constant', "anotherField"
from table2;

And I assume I need to do something like this:

insert into table1 (id, sameField, constantField, superFied)
select gen_random_uuid(), "sameField", 'constant', "anotherField"
from table2
where not exists ... ?

What I need to write instead of ? if I want this logic: check if there's already same value in sameField in table1 when selecting sameField from table2? DBMS is Postgres.

Solution

  • You can use a sub-query to see whether the record exists. You will need to define the column(s) which should be unique.

    create table table2(
id varchar(100),
sameField varchar(25),
constant varchar(25),
superField varchar(25)
);
insert into table2 values 
(gen_random_uuid(),'same1','constant1','super1'),
(gen_random_uuid(),'same2','constant2','super2')

    ✓

    2 rows affected

    create table table1(
id varchar(100),
sameField varchar(25),
constant varchar(25),
superField varchar(25)
);
insert into table1 values
(gen_random_uuid(),'same1','constant1','super1');

    ✓

    1 rows affected

    insert into table1 (id, sameField, constant, superField)
select uuid_in(md5(random()::text || clock_timestamp()::text)::cstring),
  t2.sameField, 'constant', t2.superField
from table2 t2
where sameField not in (select sameField from table1) 

    

    1 rows affected
    

    


    

    

    select * from table1;
select * from table2;

    

    id                                   | samefield | constant  | superfield
:----------------------------------- | :-------- | :-------- | :---------
4cf10b1c-7a3f-4323-9a16-cce681fcd6d8 | same1     | constant1 | super1    
d8cf27a0-3f55-da50-c274-c4a76c697b84 | same2     | constant  | super2    

id                                   | samefield | constant  | superfield
:----------------------------------- | :-------- | :-------- | :---------
c8a83804-9f0b-4d97-8049-51c2c8c54665 | same1     | constant1 | super1    
3a9cf8b5-8488-4278-a06a-fd75fa74e206 | same2     | constant2 | super2    

    

    

    db<>fiddle here