cli: function extensions.moddatetime() does not exist for Migrations
Bug report
Describe the bug
I am getting an error that function extensions.moddatetime() does not exist
To Reproduce
Steps to reproduce the behavior, please provide code snippets or a repository:
- Create an updated function with the following:
create extension if not exists moddatetime schema extensions; create trigger handle_updated_at before update on bom for each row execute procedure moddatetime (updated_at); - run: supabase db commit
updated_at - Run:
supabase stopor remove containers or start a new local dev setup - See error: Error: Error resetting database: ERROR: function extensions.moddatetime() does not exist
Expected behavior
Exepted migrations to be run successfully and updated_at migration to work
System information
- OS: macOS 12.1 M1
- Version of supabase-cli: 0.16.1
P.S
Should update the issue templete since the Nodejs CLI is Deprecated
- Version of Node.js: [e.g. 10.10.0]
About this issue
- Original URL
- State: closed
- Created 5 years ago
- Reactions: 3
- Comments: 17 (4 by maintainers)
I think I have fixed this locally for me:
I had to manually add this to the top of migration file. The migration file did not include this when auto-generated.
This bug is still happening. fwiw
I think this is similar to https://github.com/supabase/cli/issues/61.
The problem with including
CREATE EXTENSIONin a migration is you won’t be able to push the migration to the remote db, since thepostgresrole used for accessing the remote db can’t doCREATE EXTENSION.This needs to be fixed from the Supabase side, which we’re currently working on (to quote myself here, what you can do from the dashboard you should be able to do with the
postgresrole).For now though, you can write it in
supabase/extensions.sqlinstead, which is only run on the local database. You do need to manually keep it in sync with the remote db’s extensions though.@sweatybridge Thanks that have fixed the issue. However, I found other issues with that patch, like not having anymore autocomplete of table names in the SQL editor, but this is for another issue to open. Thanks for your help