Postgres Db column type issue during installation
Author: andimalajCreated Jul 31, 2026Updated Sep 18, 2026
Labelsbug
Information about bug
Hello ,
during installation of hrms with Postgres db I get an issue : The patch update_employee_advance_status generates invalid SQL for PostgreSQL — AND "return_amount" is treating a numeric column as boolean, which PostgreSQL rejects (MariaDB/MySQL allows it).
Module
HR
Version
all modules version 16
Installation method
docker
Relevant log output / Stack trace / Full Error Message.
2026-07-31T10:59:11.95013Z Updating DocTypes for hrms : [================================= ] 84%
Updating DocTypes for hrms : [================================= ] 84%
Updating DocTypes for hrms : [================================== ] 85%
Updating DocTypes for hrms : [================================== ] 85%
Updating DocTypes for hrms : [================================== ] 86%
Updating DocTypes for hrms : [================================== ] 86%
Updating DocTypes for hrms : [================================== ] 86%
Updating DocTypes for hrms : [================================== ] 87%
Updating DocTypes for hrms : [=================================== ] 87%
Updating DocTypes for hrms : [=================================== ] 88%
Updating DocTypes for hrms : [=================================== ] 88%
Updating DocTypes for hrms : [=================================== ] 89%
Updating DocTypes for hrms : [=================================== ] 89%
Updating DocTypes for hrms : [==================================== ] 90%
Updating DocTypes for hrms : [==================================== ] 90%
Updating DocTypes for hrms : [==================================== ] 90%
Updating DocTypes for hrms : [==================================== ] 91%
Updating DocTypes for hrms : [==================================== ] 91%
Updating DocTypes for hrms : [==================================== ] 92%
Updating DocTypes for hrms : [===================================== ] 92%
Updating DocTypes for hrms : [===================================== ] 93%
Updating DocTypes for hrms : [===================================== ] 93%
Updating DocTypes for hrms : [===================================== ] 93%
Updating DocTypes for hrms : [===================================== ] 94%
Updating DocTypes for hrms : [===================================== ] 94%
Updating DocTypes for hrms : [====================================== ] 95%
Updating DocTypes for hrms : [====================================== ] 95%
Updating DocTypes for hrms : [====================================== ] 96%
Updating DocTypes for hrms : [====================================== ] 96%
Updating DocTypes for hrms : [====================================== ] 96%
Updating DocTypes for hrms : [====================================== ] 97%
Updating DocTypes for hrms : [======================================= ] 97%
Updating DocTypes for hrms : [======================================= ] 98%
Updating DocTypes for hrms : [======================================= ] 98%
Updating DocTypes for hrms : [======================================= ] 99%
Updating DocTypes for hrms : [======================================= ] 99%
Updating DocTypes for hrms : [========================================] 100%
2026-07-31T10:59:11.9501702Z Setting up Frappe HR...
2026-07-31T10:59:11.9501742Z
2026-07-31T10:59:11.9501772Z Patching Existing Data...
2026-07-31T10:59:11.9501807Z Installation for Frappe HR app failed due to an error. Please try re-installing the app or report the issue on https://github.com/frappe/hrms/issues/new if not resolved.
2026-07-31T10:59:11.96898Z File "<frozen runpy>", line 198, in _run_module_as_main
2026-07-31T10:59:11.9690186Z File "<frozen runpy>", line 88, in _run_code
2026-07-31T10:59:11.9690226Z File "/home/frappe/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 177, in <module>
2026-07-31T10:59:11.969025Z main()
2026-07-31T10:59:11.9690284Z File "/home/frappe/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 77, in main
2026-07-31T10:59:11.9690321Z FrappeClickWrapper(click.Group, handle_exception)(commands=commands)(prog_name="bench")
2026-07-31T10:59:11.9690381Z File "/home/frappe/frappe-bench/env/lib/python3.14/site-packages/click/core.py", line 1514, in __call__
2026-07-31T10:59:11.9690409Z return self.main(*args, **kwargs)
2026-07-31T10:59:11.9690437Z File "/home/frappe/frappe-bench/env/lib/python3.14/site-packages/click/core.py", line 1435, in main
2026-07-31T10:59:11.9690462Z rv = self.invoke(ctx)
2026-07-31T10:59:11.9690491Z File "/home/frappe/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 48, in invoke
2026-07-31T10:59:11.9690517Z return super().invoke(ctx)
2026-07-31T10:59:11.9690546Z File "/home/frappe/frappe-bench/env/lib/python3.14/site-packages/click/core.py", line 1902, in invoke
2026-07-31T10:59:11.9690598Z return _process_result(sub_ctx.command.invoke(sub_ctx))
2026-07-31T10:59:11.9690627Z File "/home/frappe/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 48, in invoke
2026-07-31T10:59:11.9690652Z return super().invoke(ctx)
2026-07-31T10:59:11.9690681Z File "/home/frappe/frappe-bench/env/lib/python3.14/site-packages/click/core.py", line 1902, in invoke
2026-07-31T10:59:11.9690707Z return _process_result(sub_ctx.command.invoke(sub_ctx))
2026-07-31T10:59:11.9690736Z File "/home/frappe/frappe-bench/env/lib/python3.14/site-packages/click/core.py", line 1298, in invoke
2026-07-31T10:59:11.9690783Z return ctx.invoke(self.callback, **ctx.params)
2026-07-31T10:59:11.9690812Z File "/home/frappe/frappe-bench/env/lib/python3.14/site-packages/click/core.py", line 853, in invoke
2026-07-31T10:59:11.9690837Z return callback(*args, **kwargs)
2026-07-31T10:59:11.9690865Z File "/home/frappe/frappe-bench/env/lib/python3.14/site-packages/click/decorators.py", line 34, in new_func
2026-07-31T10:59:11.9690892Z return f(get_current_context(), *args, **kwargs)
2026-07-31T10:59:11.9690919Z File "/home/frappe/frappe-bench/apps/frappe/frappe/commands/__init__.py", line 28, in _func
2026-07-31T10:59:11.9690967Z ret = f(ctx.obj, *args, **kwargs)
2026-07-31T10:59:11.9690997Z File "/home/frappe/frappe-bench/apps/frappe/frappe/commands/site.py", line 522, in install_app
2026-07-31T10:59:11.9691023Z _install_app(app, verbose=context.verbose, force=force)
2026-07-31T10:59:11.9691052Z File "/home/frappe/frappe-bench/apps/frappe/frappe/installer.py", line 333, in install_app
2026-07-31T10:59:11.9691079Z frappe.get_attr(after_install)()
2026-07-31T10:59:11.9691107Z File "/home/frappe/frappe-bench/apps/hrms/hrms/install.py", line 9, in after_install
2026-07-31T10:59:11.9691312Z setup()
2026-07-31T10:59:11.9714475Z File "/home/frappe/frappe-bench/apps/hrms/hrms/setup.py", line 24, in after_install
2026-07-31T10:59:11.9714586Z run_post_install_patches()
2026-07-31T10:59:11.9714621Z File "/home/frappe/frappe-bench/apps/hrms/hrms/setup.py", line 581, in run_post_install_patches
2026-07-31T10:59:11.971465Z frappe.get_attr(f"hrms.patches.post_install.{patch_name}.execute")()
2026-07-31T10:59:11.9714682Z File "/home/frappe/frappe-bench/apps/hrms/hrms/patches/post_install/update_employee_advance_status.py", line 16, in execute
2026-07-31T10:59:11.9714736Z ).run()
2026-07-31T10:59:11.9714768Z File "/home/frappe/frappe-bench/apps/frappe/frappe/query_builder/utils.py", line 131, in execute_query
2026-07-31T10:59:11.9714796Z result = frappe.local.db.sql(query, params, *args, **kwargs) # nosemgrep
2026-07-31T10:59:11.9714825Z File "/home/frappe/frappe-bench/apps/frappe/frappe/database/postgres/database.py", line 234, in sql
2026-07-31T10:59:11.9714854Z return super().sql(modify_query(query), modify_values(values), *args, **kwargs)
2026-07-31T10:59:11.9714883Z File "/home/frappe/frappe-bench/apps/frappe/frappe/database/database.py", line 294, in sql
2026-07-31T10:59:11.9714931Z traceback.print_stack()
2026-07-31T10:59:11.9714956Z Error in query:
2026-07-31T10:59:11.9714984Z argument of AND must be type boolean, not type numeric
2026-07-31T10:59:11.9715013Z LINE 1: ...ET "status"='Returned' WHERE "docstatus"= '1' AND "return_am...
2026-07-31T10:59:11.9715041Z ^
2026-07-31T10:59:11.9715064Z
2026-07-31T10:59:14.3328751Z An error occurred while installing hrms: argument of AND must be type boolean, not type numeric
2026-07-31T10:59:14.332915Z LINE 1: ...ET "status"='Returned' WHERE "docstatus"= '1' AND "return_am...
2026-07-31T10:59:14.3329189Z ^
2026-07-31T10:59:14.3329302Z
2026-07-31T10:59:14.3329344Z Traceback with variables (most recent call last):
2026-07-31T10:59:14.3329373Z File "apps/frappe/frappe/commands/site.py", line 522, in install_app
2026-07-31T10:59:14.33294Z _install_app(app, verbose=context.verbose, force=force)
2026-07-31T10:59:14.332943Z context = CliCtxObj(force=False, profile=False, sites=['erp.localhost'], verbose=False)
2026-07-31T10:59:14.3329455Z apps = ('hrms')
2026-07-31T10:59:14.3329479Z force = False
2026-07-31T10:59:14.3329532Z _install_app = <function install_app at 0x722e4916a610>
2026-07-31T10:59:14.332956Z filelock = <function filelock at 0x722e49458930>
2026-07-31T10:59:14.3329584Z exit_code = 0
2026-07-31T10:59:14.3329696Z site = 'erp.localhost'
2026-07-31T10:59:14.3329722Z app = 'hrms'
2026-07-31T10:59:14.3329749Z err = argument of AND must be type boolean, not type numeric
2026-07-31T10:59:14.3329778Z LINE 1: ...ET "status"='Returned' WHERE "docstatus"= '1' AND "return_am...
2026-07-31T10:59:14.3329829Z ^
2026-07-31T10:59:14.3329853Z
2026-07-31T10:59:14.332988Z File "apps/frappe/frappe/installer.py", line 333, in install_app
2026-07-31T10:59:14.3329905Z frappe.get_attr(after_install)()
2026-07-31T10:59:14.332993Z name = 'hrms'
2026-07-31T10:59:14.3329953Z verbose = False
2026-07-31T10:59:14.3329979Z set_as_patched = True
2026-07-31T10:59:14.3330001Z force = False
2026-07-31T10:59:14.3330054Z sync_jobs = <function sync_jobs at 0x722e481214e0>
2026-07-31T10:59:14.3330081Z sync_for = <function sync_for at 0x722e48122a30>
2026-07-31T10:59:14.333011Z sync_customizations = <function sync_customizations at 0x722e494beb90>
2026-07-31T10:59:14.3330136Z sync_fixtures = <function sync_fixtures at 0x722e48122f00>
2026-07-31T10:59:14.3330163Z installed_apps = ['frappe', 'erpnext']
2026-07-31T10:59:14.3330187Z app = 'frappe/erpnext'
2026-07-31T10:59:14.3330211Z required_app = 'erpnext'
2026-07-31T10:59:14.3330281Z other_class_overrides = []
2026-07-31T10:59:14.3330308Z after_install = 'hrms.install.after_install'
2026-07-31T10:59:14.33304Z app_hooks = {'accounting_dimension_doctypes': ['Expense Claim', 'Expense Claim Detail', 'Expense Taxes and Charges', 'Payroll Entry', 'Leave Encashment'], 'add_to_apps_screen': [{'name': 'hrms', 'logo': '/assets/hrms/images/frappe-hr-logo.svg', 'title': 'Frappe HR', 'route': '/desk/people', 'has_permission': 'hrms.hr.utils.check_app_permission'}], 'advance_payment_payable_doctypes': ['Leave Encashment', 'Gratuity', 'Employee Advance'], 'after_app_install': ['hrms.setup.after_app_install'], 'after_install': ['hrms.install.after_install'], 'after_migrate': ['hrms.setup.update_select_perm_after_install'], 'app_description': ['Modern HR and Payroll Software'], 'app_email': ['[email protected]'], 'app_home':...rd_complete': ['hrms.subscription_utils.update_erpnext_access'], 'source_link': ['http://github.com/frappe/hrms'], 'website_generators': ['Job Opening'], 'website_route_rules': [{'from_route': '/hrms/<path:app_path>', 'to_route': 'hrms'}, {'from_route': '/hr/<path:app_path>', 'to_route': 'roster'}]}
2026-07-31T10:59:14.333045Z File "apps/hrms/hrms/install.py", line 21, in after_install
2026-07-31T10:59:14.3330679Z raise e
2026-07-31T10:59:14.3330711Z BUG_REPORT_URL = 'https://github.com/frappe/hrms/issues/new'
2026-07-31T10:59:14.3330736Z File "apps/hrms/hrms/install.py", line 9, in after_install
2026-07-31T10:59:14.3330758Z setup()
2026-07-31T10:59:14.3330782Z BUG_REPORT_URL = 'https://github.com/frappe/hrms/issues/new'
2026-07-31T10:59:14.3330807Z File "apps/hrms/hrms/setup.py", line 24, in after_install
2026-07-31T10:59:14.3330854Z run_post_install_patches()
2026-07-31T10:59:14.3330881Z File "apps/hrms/hrms/setup.py", line 581, in run_post_install_patches
2026-07-31T10:59:14.3330906Z frappe.get_attr(f"hrms.patches.post_install.{patch_name}.execute")()
2026-07-31T10:59:14.3331283Z POST_INSTALL_PATCHES = ('erpnext.patches.v13_0.move_tax_slabs_from_payroll_period_to_income_tax_slab', 'erpnext.patches.v13_0.move_doctype_reports_and_notification_from_hr_to_payroll', 'erpnext.patches.v13_0.move_payroll_setting_separately_from_hr_settings', 'erpnext.patches.v13_0.update_start_end_date_for_old_shift_assignment', 'erpnext.patches.v13_0.updates_for_multi_currency_payroll', 'erpnext.patches.v13_0.update_reason_for_resignation_in_employee', 'erpnext.patches.v13_0.set_company_in_leave_ledger_entry', 'erpnext.patches.v13_0.rename_stop_to_send_birthday_reminders', 'erpnext.patches.v13_0.set_training_event_attendance', 'erpnext.patches.v14_0.set_payroll_cost_centers', 'erpnext.patches.v13_0.update_empl...nce_status', 'erpnext.patches.v13_0.update_expense_claim_status_for_paid_advances', 'erpnext.patches.v14_0.delete_employee_transfer_property_doctype', 'erpnext.patches.v13_0.set_payroll_entry_status', 'create_country_fixtures', 'update_allocate_on_in_leave_type', 'update_performance_module_changes')
2026-07-31T10:59:14.3331347Z patch = 'erpnext.patches.v13_0.update_employee_advance_status'
2026-07-31T10:59:14.3331372Z patch_name = 'update_employee_advance_status'
2026-07-31T10:59:14.33314Z File "apps/hrms/hrms/patches/post_install/update_employee_advance_status.py", line 16, in execute
2026-07-31T10:59:14.3331422Z ).run()
2026-07-31T10:59:14.3331445Z advance = "tabEmployee Advance"
2026-07-31T10:59:14.3331472Z File "apps/frappe/frappe/query_builder/utils.py", line 131, in execute_query
2026-07-31T10:59:14.3331522Z result = frappe.local.db.sql(query, params, *args, **kwargs) # nosemgrep
2026-07-31T10:59:14.3331555Z query = 'UPDATE "tabEmployee Advance" SET "status"=%(param1)s WHERE "docstatus"=1 AND "return_amount" AND "paid_amount"="return_amount" AND "status"=%(param2)s'
2026-07-31T10:59:14.3331581Z args = ()
2026-07-31T10:59:14.3331603Z kwargs = {}
2026-07-31T10:59:14.3331624Z dt = None
2026-07-31T10:59:14.3331646Z fields = []
2026-07-31T10:59:14.333167Z child_queries = "_child_queries"
2026-07-31T10:59:14.3331716Z params = {'param1': 'Returned', 'param2': 'Paid'}
2026-07-31T10:59:14.3331743Z File "apps/frappe/frappe/database/postgres/database.py", line 234, in sql
2026-07-31T10:59:14.3331769Z return super().sql(modify_query(query), modify_values(values), *args, **kwargs)
2026-07-31T10:59:14.3331848Z self = frappe.database.postgres.database.PostgresDatabase(after_commit=frappe.utils.CallbackManager(-), after_rollback=frappe.utils.CallbackManager(-), auto_commit_on_many_writes=0, before_commit=frappe.utils.CallbackManager(-), before_rollback=frappe.utils.CallbackManager(-), cur_db_name='erpnext_prod', db_type='postgres', host='10.0.1.5', logger=<Logger database-erp.localhost (WARNING)>, password='Porsha2026!', port=5432, socket=None, transaction_writes=1033, type_map={'Currency': ('decimal', '21,9'), 'Int': ('int', None), 'Long Int': ('bigint', None), 'Float': ('decimal', '21,9'), 'Percent': ('decimal', '21,9'), 'Check': ('smallint', None), 'Small Text': ('text', ''), 'Long Text': ('text', ''...ict{('name'): [frappe.types.frappedict._dict{'name': 'Payment Entry'}]}, 'Purchase Receipt': collections.defaultdict{('name'): [frappe.types.frappedict._dict{'name': 'Purchase Receipt'}]}, 'Expense Claim': collections.defaultdict{('name'): [frappe.types.frappedict._dict{'name': 'Expense Claim'}]}}})
2026-07-31T10:59:14.3331905Z query = 'UPDATE "tabEmployee Advance" SET "status"=%(param1)s WHERE "docstatus"=1 AND "return_amount" AND "paid_amount"="return_amount" AND "status"=%(param2)s'
2026-07-31T10:59:14.3331933Z values = {'param1': 'Returned', 'param2': 'Paid'}
2026-07-31T10:59:14.3331988Z args = ()
2026-07-31T10:59:14.3332012Z kwargs = {}
2026-07-31T10:59:14.3332037Z __class__ = frappe.database.postgres.database.PostgresDatabase
2026-07-31T10:59:14.3332062Z File "apps/frappe/frappe/database/database.py", line 272, in sql
2026-07-31T10:59:14.3332085Z self.execute_query(query, values)
2026-07-31T10:59:14.3332184Z self = frappe.database.postgres.database.PostgresDatabase(after_commit=frappe.utils.CallbackManager(-), after_rollback=frappe.utils.CallbackManager(-), auto_commit_on_many_writes=0, before_commit=frappe.utils.CallbackManager(-), before_rollback=frappe.utils.CallbackManager(-), cur_db_name='erpnext_prod', db_type='postgres', host='10.0.1.5', logger=<Logger database-erp.localhost (WARNING)>, password='Porsha2026!', port=5432, socket=None, transaction_writes=1033, type_map={'Currency': ('decimal', '21,9'), 'Int': ('int', None), 'Long Int': ('bigint', None), 'Float': ('decimal', '21,9'), 'Percent': ('decimal', '21,9'), 'Check': ('smallint', None), 'Small Text': ('text', ''), 'Long Text': ('text', ''...ict{('name'): [frappe.types.frappedict._dict{'name': 'Payment Entry'}]}, 'Purchase Receipt': collections.defaultdict{('name'): [frappe.types.frappedict._dict{'name': 'Purchase Receipt'}]}, 'Expense Claim': collections.defaultdict{('name'): [frappe.types.frappedict._dict{'name': 'Expense Claim'}]}}})
2026-07-31T10:59:14.3332219Z query = 'UPDATE "tabEmployee Advance" SET "status"=%(param1)s WHERE "docstatus"= '1' AND "return_amount" AND "paid_amount"="return_amount" AND "status"=%(param2)s'
2026-07-31T10:59:14.3332244Z values = {'param1': 'Returned', 'param2': 'Paid'}
2026-07-31T10:59:14.3332266Z as_dict = 0
2026-07-31T10:59:14.3332306Z as_list = 0
2026-07-31T10:59:14.3332328Z debug = False
2026-07-31T10:59:14.333235Z ignore_ddl = 0
2026-07-31T10:59:14.3332372Z auto_commit = 0
2026-07-31T10:59:14.3332394Z update = None
2026-07-31T10:59:14.3332416Z explain = False
2026-07-31T10:59:14.3332438Z run = True
2026-07-31T10:59:14.3332459Z pluck = False
2026-07-31T10:59:14.3332481Z as_iterator = False
2026-07-31T10:59:14.3332526Z query_type = 'update'
2026-07-31T10:59:14.3332548Z trace_id = None
2026-07-31T10:59:14.3332574Z File "apps/frappe/frappe/database/database.py", line 372, in execute_query
2026-07-31T10:59:14.3332598Z return self._cursor.execute(query, values)
2026-07-31T10:59:14.3332726Z self = frappe.database.postgres.database.PostgresDatabase(after_commit=frappe.utils.CallbackManager(-), after_rollback=frappe.utils.CallbackManager(-), auto_commit_on_many_writes=0, before_commit=frappe.utils.CallbackManager(-), before_rollback=frappe.utils.CallbackManager(-), cur_db_name='erpnext_prod', db_type='postgres', host='10.0.1.5', logger=<Logger database-erp.localhost (WARNING)>, password='Porsha2026!', port=5432, socket=None, transaction_writes=1033, type_map={'Currency': ('decimal', '21,9'), 'Int': ('int', None), 'Long Int': ('bigint', None), 'Float': ('decimal', '21,9Source: frappe/hrms