This is the view:
SELECT DISTINCT d.comic_book_id as comic_book_id,
d.comic_detail_id as comic_detail_id,
d.archive_type as archive_type,
d.comic_state as comic_state,
CASE
WHEN EXISTS(SELECT *
FROM comic_metadata_sources s
WHERE s.comic_book_id = d.comic_book_id)
THEN false
ELSE true END AS is_unscraped,
d.comic_type as comic_type,
d.publisher as publisher,
d.series as series,
d.volume as volume,
d.issue_number as issue_number,
d.description as description,
d.notes as notes,
(SELECT RIGHT(CONCAT('0000000000', d.issue_number), 10)) as sortable_issue_number, d.title as title, (
SELECT COUNT(*)
FROM comic_pages cp
WHERE cp.comic_book_id = d.comic_book_id) as page_count
, d.cover_date as cover_date
, CASE WHEN d.cover_date IS NULL THEN 0 ELSE MONTH (cover_date)
END
as month_published
, CASE WHEN d.cover_date IS NULL THEN 0 ELSE YEAR (cover_date)
END
as year_published,
d.store_date as store_date,
d.added_date as added_date
FROM comic_details d
In v2 of our project, this view created and worked just fine.
I'm currently working on v3 of the project, and part of the requirement is to provide a new set of migrations to let users create the tables from scratch and have them look identical to what an existing user would get. So I copied the contents of the view from our v2 codebase into the migration for v3.
When I run the migration on all other supported databases (H2, MySQL, Mariadb) it, and all related operations, works fine.
But, when I run it on Postgres, it errors out with:
Caused by: org.postgresql.util.PSQLException: ERROR: function month(date) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
But, when I go back to our v2 code and run the exact same Liquibase migration content it works just fine, no problems. I even copied the body of the old migration into the new one and it fails. But the old code still works on the same Postgres instance and creates a working view.
I tried replacing the call to month()
and year()
with:
THEN SELECT EXTRACT(MONTH FROM cover_date)
and:
THEN SELECT EXTRACT(YEAR FROM cover_date)
The view gets created fine then, but the runtime replaces them with a call to MONTH()
and YEAR()
respectively and Postgres again gives the same error message.
Another difference between our v2 and v3 code is that v3 is that we migrated from Spring Boot 3.2.3 to 3.4.1. I'm not sure if the Liquibase version changed between them, but could there be some kind of bug in the newer LB version? I'm assuming Spring bumped it to a newer version.
Ultimately I modified the view to use:
EXTRACT(month from cover_date)
And found that one of the classes had an instance variable that uses was annotated with a formula that was also using the month() function. Updating that to use the above fixed all the issues.