What's the proper way to conditionallly create a view?

The suggestion to have two changesets with the two different definitions is what I would recommend. You can use multiple dbms names separated by commas to show that the changeset should apply to any in the list, and you can also use ! to indicate not - so you can do things like use dbms=“postgresql” on one changeset and dbms="!postgresql" on another. 

Steve Donie
Principal Software Engineer
Datical, Inc. http://www.datical.com/

Change the dbms attribute will not affect the checksum of a changeset. This is also true for the labels and contexts attributes. 

Steve Donie
Principal Software Engineer
Liquibase Community Engagement
Datical, Inc. http://www.datical.com/

I use derby and PostgreSQL and they have different syntaxes for aggregating over years and months (“YEAR(timestamp)” vs. “extract(year from timestamp) as year” (both result in a column name year or YEAR to be used in the query).



Derby do it this way:
select sum(t.transaction_amount), extract(year from t.transaction_time) as year, extract(month from t.transaction_time) as month from transactions t join transaction_types tt on tt.transaction_type_id=t.transaction_type_id join accounts a on a.account_id=t.account_id where tt.transaction_is_work and a.username=? group by extract(year from t.transaction_time), extract(month from t.transaction_time) order by extract(year from t.transaction_time), extract(month from t.transaction_time)
What's the best way to create a view conditionally based on the dbms used? 

Is there some kind of if-then-else logic? (I have googled, but haven't found)

The only way I can think about is to have two separate changesets for creating the same view with a precondition on the dbms used...?

Also, instead of selecting on dbms, I would have preferred to select on capability so that instead of creating a view just for derby I would create a view for all dbms'es that follow the same syntax (and ditto for PostgreSQL). Is this possible?

  Also, instead of selecting on dbms, I would have preferred to select on capability so that instead of creating a view just for derby I would create a view for all dbms’es that follow the same syntax (and ditto for PostgreSQL). Is this possible?

Is it possible to eg. use sqlCheck and continue only if the SQL doesn't fail with a syntax error?

Thanks for the feedback!

I don’t know which DBMSes that use the same syntax as derby…?  Maybe all/most of them, but “!postgresql” seems a bit drastic…? 

Will changing the dbms list in the precondition change the checksum of the changeset? Ie. can I add a dbms without breaking on installations that already have applied the changeset?

  I don’t know which DBMSes that use the same syntax as derby…?  Maybe all/most of them, but “!postgresql” seems a bit drastic…?  

(According to a bit of googling, MySQL and MSSQL use the same syntax as derby (or at least: looks very similar), while e.g. Oracle has a third variant. )

 The added changesets supporting derby and PostgreSQL, can be seen here.