CODEWITHSUNDEEP
sqloracle-databasepostgresqlliquibase
mara122
mara122

Reputation: 323

Inserting values if they don't already exist - ORACLE/Postgres

I have rollback in liquibase like:

<changeSet author="..." id="...">
        <preConditions onFail="MARK_RAN">
            <sqlCheck expectedResult="1">
                ...
            </sqlCheck>
        </preConditions>
        <delete tableName="A">
            <where>ID = 'house'</where>
        </delete>
        <rollback>
            <insert tableName="A">
                <column name="ID" value="house" />
                <column name="TITLE" value="a" />
                <column name="TYPE" value="b" />
                <column name="VER" valueNumeric="c" />
            </insert>
        </rollback>
    </changeSet>

and this changset is run in 3 different xml files, same for every file.

I need to provide custom sql query to rollback as it can throw exception about inserting same value to DB so I need to know wheter ID with given value exists.

I have made something like this:

<rollback>
            <sql dbms="oracle">
                insert into A(ID,TITLE,TYPE,VER)
                VALUES(house,a,b,c)
                where not exists (select ID from A where ID = 'house')
            </sql>
            <sql dbms="postgresql">
                ...
            </sql>
        </rollback>

I have made something like this, but it does not work. (I need to to this also for postgres) How can I do this? I need only to check whether ID with given value exists in 'A' table.

Upvotes: 0

Views: 805

Answers (2)

user330315
user330315

Reputation:

Use a SELECT as the source, not the values clause:

For Postgres:

INSERT INTO A(ID,TITLE,TYPE,VER)
select 'house','a','b','c'
WHERE NOT EXISTS (SELECT 1 from A where ID = 'house');

db fiddle

For Oracle:

INSERT INTO A(ID,TITLE,TYPE,VER)
select 'house','a','b','c'
from dual
WHERE NOT EXISTS (SELECT 1 from A where ID = 'house');

db fiddle

Upvotes: 1

Konstantin Sch&#252;tte
Konstantin Sch&#252;tte

Reputation: 1009

INSERT INTO A(ID,TITLE,TYPE,VER)
VALUES(house,a,b,c)
WHERE NOT EXISTS (SELECT 1 from A where ID = 'house')

Should work for postgres

Upvotes: 0

Related Questions