Check if column exists in a precondition

General Discussion
1

I need advice on how to do this…

I want to use a precondition to check if a column exists.  The column is created and defaulted to NULL.

Ideally, I’d use the columnExists precondition, but I’d need to know my DB’s schema.  It’s possible that there might be multiple schemas in this DB, so is there a way to dynamically reference the one that liquibase is currently connected to?

Otherwise, I could use the sqlCheck precondition, but I don’t know what my expectedResult should be.  I really need the expected result to be 0 or greater, because of the null values as default, but is there a way to specify that?  Is there a way to catch the “Invalid column name” exception that a DB will throw if I attempt to run a select count(column) on a column that doesn’t exist?

Please advise,

Aaron

2

If you don’t specify the schemaName in the columnExists precondition, it will check the default schema, which is the one that liquibase is running against. 

To use the sqlCheck precondition, you would need to have a query that returns a single value, like “select count(*) from information_schema.columns where column_name=‘YOUR_COLUMN’ and schema_name=‘YOUR_SCHEMA’”.  The trouble is that you need to reference your schema in this example.  You may be able to come up with a different metadata query that will get it, though, depending on your database.  Querying your database information_schema is a better approach than trying to query the table directly.

Nathan

3

Ideally, I’d like to use the columnExists check, so I’ll give that a try.

The confusing thing to me was that the documentation says that schemaName is required, but you alluded to the fact that I could leave it blank and it’ll use the one that liquibase is running against.  That’s exacty what I want, so I’ll see how it goes and post my results.

Thanks.

4

It looks like a documentation bug.  Let me know if it doesn’t work.

Nathan

5

Well, it all worked as expected…but it didn’t fulfill my need.

My changeset was adding a column.  So, I wanted to skip the changeset if the columnExisted.  Checking for columnExists - if that returned a true value, that’s when I wanted to skip the changeset because it had already been updated.  But, this also meant that the precondition was satisfied and in that case, I didn’t see a way to skip the changeset.  I could only skip the changeset if my precondition wasn’t met.

Therefore, to get around it, I did a simple select from the DATABASECHANGELOG and looked for the ID of the changeset that was in a previous run and my expected result was 0.  Therefore, if that select returned a 1, then I knew it had already run and I could skip the changeset.

So, I accomplished my goal, but believe it would have been cleaner if there was some way to do a “onSuccess” thing for a precondition too.  Just a suggestion…

Thanks for the help!

Aaron

6

There is a precondition you can use as well, so you can say:

 

The onFail=MARK_RAN is used for situations like yours where if the precondition fails you want to assume the changeSet has already been run.

Nathan

7



I ran into a strange problem using a precondition shown below:

 
       
           
       
       
           
       
       
           
       
   

The error shown:

…
liquibase.exception.DatabaseException: Error executing SQL ALTER TABLE COLL_N_INSTR ADD FI_DATA_CFIVALUE VARCHAR2(6): ORA-01430: column being added already exists in table
…

Which is of course what I’m trying to avoid…

8

I’m horribly sorry…  It’s a different changelist that was being used with the same ID as the one that I thought was failing.   Better to delete the post above…

9

Thanks for the update. I’ll leave the post, though, in case it helps with troubleshooting for someone else.


Nathan

10

Actually it helped , thanks alot