[Liquibase-user] postgresql: create index if not exist

[Mark Wigmans]

Hello,
I'am evaluating liquibase (version 1.8.1) and I'am strugling to get it work.

I'am able to create an index via:

<changeSet id="1" author="mawi">
<createIndex tableName="account" indexName="idx_account_state">
<column name="c_state"/>
</createIndex>
</changeSet>

This works. However I only want to create this index when it not already
exists. Whatever I try it keep saying (via the logging) that the index
'idx_account_state' does not exists. It doesn't matter if the index exists
or not.
So I think I'am missing something as a first time liquibase user.

I would expect something like:

<changeSet id="1" author="mawi"> <preConditions onFail="WARN"> <not>
<indexExists indexName="idx_account_state"/> </not> </preConditions>
<createIndex tableName="account" indexName="idx_account_state"> <column
name="c_state"/> </createIndex> </changeSet>

However, I got errors because the index is always created :-(
I think that I'am able to recreate the initial start condition by removing
the databasechangelog* tables OR use a new (higher) changeSet id.

So, please help.

environment:
postgresql: 8.2
liquibase: 1.8.1
java: 1.6

Regards,
Mark Wigmans

PS The tag indexExists says that a 'schemaName' field is required. However
what should the value be, I don't use a schemaName when the index is
created.

[Mark Wigmans]

Hello,
I'am evaluating liquibase and I'am strugling to get it work.

I'am able to create an index via:

<changeSet id="1" author="mawi">
<createIndex tableName="account" indexName="idx_account_state">
<column name="c_state"/>
</createIndex>
</changeSet>

This works. However I only want to create this index when it not already
exists. Whatever I try it keep saying (via the logging) that the index
'idx_account_state' does not exists. It doesn't matter if the index exists
or not.
So I think I'am missing something as a first time liquibase user.

I would expect something like:

<changeSet id="1" author="mawi"> <preConditions onFail="WARN"> <not>
<indexExists indexName="idx_account_state"/> </not> </preConditions>
<createIndex tableName="account" indexName="idx_account_state"> <column
name="c_state"/> </createIndex> </changeSet>

However, I got errors because the index is always created :-(
I think that I'am able to recreate the initial start condition by removing
the databasechangelog* tables OR use a new (higher) changeSet id.

So, please help.

Regards,
Mark Wigmans

PS The tag indexExists says that a 'schemaName' field is required. However
what should the value be, I don't use a schemaName when the index is
created.

[Voxland, Nathan]

indexExists should not require the schemaName to be set.



I think your trouble is that you are using onFail="WARN". That will
output that the precondition failed but then continue on running the
changes. Try changing it to onFail="MARK_RAN"



Nathan



From: Mark Wigmans [mailto:***@computer.org]
Sent: Wednesday, October 15, 2008 2:30 PM
To: liquibase-***@lists.sourceforge.net
Subject: Re: [Liquibase-user] postgresql: create index if not exist



Hello,



I'am evaluating liquibase (version 1.8.1) and I'am strugling to get it
work.



I'am able to create an index via:



<changeSet id="1" author="mawi">

<createIndex tableName="account" indexName="idx_account_state">

<column name="c_state"/>

</createIndex>

</changeSet>



This works. However I only want to create this index when it not already
exists. Whatever I try it keep saying (via the logging) that the index
'idx_account_state' does not exists. It doesn't matter if the index
exists or not.

So I think I'am missing something as a first time liquibase user.



I would expect something like:



<changeSet id="1" author="mawi"> <preConditions onFail="WARN"> <not>
<indexExists indexName="idx_account_state"/> </not> </preConditions>
<createIndex tableName="account" indexName="idx_account_state"> <column
name="c_state"/> </createIndex> </changeSet>



However, I got errors because the index is always created :-(

I think that I'am able to recreate the initial start condition by
removing the databasechangelog* tables OR use a new (higher) changeSet
id.



So, please help.



environment:

postgresql: 8.2

liquibase: 1.8.1

java: 1.6



Regards,

Mark Wigmans



PS The tag indexExists says that a 'schemaName' field is required.
However what should the value be, I don't use a schemaName when the
index is created.